CODEWITHSUNDEEP
vbaexcel
Tomaski
Tomaski

Reputation: 497

Is it possible to use auto-filter or find on a dictionary?

so I have a userform with comboBox serving as a dynamic-search box.

The data needed to be searched is located in another workbook (1200+ rows). To avoid constant opening and closing of that data-workbook, I load it all into dictionary during form initialization.

Now my question is: is it possible to quickly filter out dictionary data (and update combobox), as user is typing or do I need to change my approach?

any help would be greatly appreciated.

here is the code I have so far:

Option Explicit
Private emplDict As Object
'all other constants and functions are declared in a separate module named "code"
Private Sub btnClose_Click()
    Unload Me
End Sub
Private Sub comboSearch_Change()
    Me.comboSearch.DropDown
End Sub
Private Sub UserForm_Initialize()
    Dim xlWS As Worksheet
    Dim xlWB As Workbook
    Dim rng As Range
    Dim lstRw As Long
    Dim item As Variant

    Application.Run "code.xlHelper", False ' turn off screen updating, alerts, events

    Set emplDict = CreateObject("scripting.dictionary")
    Set xlWB = Workbooks.Open(Filename:=SUB_PLANNING & EMPLOYEE_LIST)
    Set xlWS = xlWB.Sheets("namen_werknemers")

    With xlWS
        lstRw = .Cells(Rows.Count, 1).End(xlUp).Row
        Set rng = .Range(.Cells(2, 1), .Cells(lstRw, 1))
    End With

    For Each item In rng
        If Not emplDict.exists(item.Value) Then
            emplDict.Add item.Text, item.Offset(0, 1).Text
        End If
    Next

    xlWB.Close False

    Set xlWS = Nothing
    Set xlWB = Nothing

    Application.Run "code.xlHelper", True
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Set emplDict = Nothing
End Sub

Upvotes: 2

Views: 3164

Answers (2)

user6432984
user6432984

Reputation:

The Key is to use the Dictionary's Keys.

Use the VBA Filter method to return an array of filtered Keys.

enter image description here

Private EEDict As Object

Private Sub cboEEList_Change()
    Dim Keys
    Keys = EEDict.Keys
    cboEEList.List = Filter(Keys, cboEEList.Text, True, vbTextCompare)
    cboEEList.DropDown

End Sub

Private Sub UserForm_Initialize()
    Dim arData
    Dim x As Long

    Set EEDict = CreateObject("scripting.dictionary")

    arData = Worksheets("Employees").Range("A1").CurrentRegion.Value2

    For x = 2 To UBound(arData)
        EEDict(arData(x, 1)) = arData(x, 2)
    Next

    cboEEList.List = EEDict.Keys
End Sub

I got the sample data from: Fusion Tables - Employees.csv

Upvotes: 7

Nathan_Sav
Nathan_Sav

Reputation: 8531

A different approach, but a little messy would be a recordset, something like this, you'd need to add the excel ranges to it, where I've cobbled some values in.

Option Explicit

Private rs As ADODB.Recordset

Private Sub ComboBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

Set rfiltered = New ADODB.Recordset
rs.Filter = "Value like '" & Chr(KeyAscii) & "*'"

If Not rs.EOF Then
    Range("e1").CopyFromRecordset rs
    Me.ComboBox1.RowSource = "E1:E10"
End If

End Sub


Private Sub UserForm_Initialize()

Set rs = New ADODB.Recordset
Dim fieldsArray(1) As Variant
Dim values(1) As Variant

rs.Fields.Append "Key", adVarChar, 5
rs.Fields.Append "Value", adVarChar, 5

fieldsArray(0) = "Key"
fieldsArray(1) = "Value"

values(0) = 4
values(1) = "as"

rs.Open
rs.AddNew fieldsArray, values
rs.Update

End Sub

Upvotes: 0

Related Questions