Excel VBA for searchable DV in multiple rows to popup list based on another sheet - Stack Overflow

admin管理员组

文章数量:1123600

I am looking the inventory and DR/CR ledgers in which I have to enter customer/supplier/item name every time manually which causes spelling mistakes in typing. So, I want to search the name in drop down list and choose them.

I have tried this code found in this forum but it is not searchable. This displays whole list.

Sub setupDV()
Dim rSource As Range, rDV As Range, r  As Range, csString As String
Dim c As Collection

    Set rSource = Sheets("Sheet2").Range("B1:B1000")
    Set rDV = Sheets("Sheet1").Range("C5")
    Set c = New Collection
    csString = ""
    On Error Resume Next
    For Each r In rSource
        v = r.Value
        If v <> "" Then
            c.Add v, CStr(v)
            If Err.Number = 0 Then
                If csString = "" Then
                    csString = v
                Else
                    csString = csString & "," & v
                End If
            Else
                Err.Number = 0
            End If
        End If
    Next r
    On Error GoTo 0
    
    'MsgBox csString
    
    With rDV.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=csString
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = False
    End With

End Sub

本文标签： Excel VBA for searchable DV in multiple rows to popup list based on another sheetStack Overflow