vba在excel中创建下拉框

 

在Sheet2中把C1:C7的区域命名为code,在sheet1中给“A1”cell加下拉框,下拉框的内容用的是区域code的内容

Sub Macro1()
    Dim s As String
    s = setName(3, "code")
    Sheet1.Range("A1").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=code"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = "Options"
        .ErrorTitle = ""
        .InputMessage = "Correct Division/Department"
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    Range("C1:C8").Select
    Sheets("Sheet1").Select
    ActiveWindow.SmallScroll Down:=-12
    Range("A1").Select
    Application.WindowState = xlMinimized
    Application.WindowState = xlNormal
    Application.WindowState = xlMinimized
End Sub

Function setName(column As Integer, range_name As String) As String
    Dim N As Integer
    Dim K As Integer
    Dim refers As String
   
    K = 0
    For N = 1 To Sheet2.UsedRange.Count
   
        If (Sheet2.Cells(N, 3) <> "") Then
            K = K + 1
        Else
            Exit For
        End If
    Next N
    refers = "=Sheet2!R1C" & column & ":R" & K & "C" & column
    ActiveWorkbook.Names.Add Name:=range_name, RefersToR1C1:=refers
    setName = ""
End Function

阅读更多
个人分类: Excel
上一篇excel2007里面怎么插入控件
下一篇拷贝区域名称
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭