自定义Excel的选择项

示例，有任意数量的选择项放在 表 set的A列，先自定义名称 xselect,代码如下

i = .Range("65536").End(xlUp).Row
j = 1
ActiveWorkbook.Names.Add Name:="xselect", RefersToR1C1:="=set!R2C" & CStr(j) & ":R" & CStr(i) & "C" & CStr(j)

做自定义函数，给指定区域加上选择项

Public Sub SetSelect(xRange, xName)
x = ActiveCell.Row
y = ActiveCell.Column '取得原光标所在单元格
xRange.Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=" & xName
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .IMEMode = xlIMEModeNoControl
        .ShowInput = True
        .ShowError = True
    End With
    Cells(x, y).Activate '光标回原位置
End Sub

调用，给 A2:A5，加上刚才的选择内容

Call SetSelect(Range("A2:A5"), "xselect")

这样在A2:A5就可以选择刚才在set表A列的那些选择项了。