在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