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 = x