jkluio668
2/20/2018 - 1:09 PM

Validation_from_data_arr

数据有效性、数效

sub add validation1()
    arr1 = get_data_arr("data_1", 1)
    str1 = Join(arr1, ",")
    for i = 1 to x1 
        cells(i, x2).selection
        add_validation2 str1
    next
end sub
function add_validation2(str1)
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:=str1 '只要改此处
        .IgnoreBlank = True
        .InCellDropdown = True
        .IMEMode = xlIMEModeNoControl
        .ShowInput = True
        .ShowError = False
    End With
end function

'可以用字典代替数组,作为数效的数据源:
       arr = Range()
       For i = 1 To UBound(arr)
           d(arr(i, 1)) = ""
       Next
       With Selection.Validation
            .Delete
            .Add Type:=3, AlertStyle:=1, Operator:=1, Formula1:=Join(d.keys, ",")
       End With