vba-加数据验证

Private Sub CommandButton1_Click()
'给商品代码下拉列表赋值
    Dim tb As ListObject
    Set tb = Sheet1.ListObjects("表_入库")
    Dim S01 As Object
    Set S01 = Sheets("Config")
    Dim common As New common
    Dim rMax As Long
    rMax = common.getLastRow(S01, "A") 'S01.Range("A65535").End(xlUp).Row
             
    With Sheet1.Range("D8:D" & 7 + tb.ListRows.Count).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=" & S01.Name & & S01.Range("A2:A"&rmax).AddressEnd With
End Sub

 

Function getLastRow(ByVal sheetName As Worksheet, ByVal columnName As String) As Long
    Dim maxRow As Long
    If Right(rptfile, 3) = "xls" Then
        maxRow = sheetName.[A65536].End(xlUp).Row
    Else
        maxRow = sheetName.[A1048576].End(xlUp).Row
    End If
    getLastRow = maxRow
End Function

 vba函数 evaluate()执行excel函数

'查找提交的数据是否在config数据列中
Function IsExistInConfig(ByVal target As String)
    Dim S01 As Object
    Set S01 = Sheets("Config")
    
    If S01.Range("A:A").Find(What:=target, LookAt:=xlWhole) Is Nothing Then
        IsExistInConfig = False
        Else
        IsExistInConfig = True
    End If
End Function