I am using a combobox on a userform, and I have added a list to it by using a dictionary (from Range).
I need the combobox to show partial string match as I am typing onto it, So I have used this code:
With Me.ComboBox1
.List = Filter(dic.Keys, .Text, True, vbTextCompare)
.DropDown 'Expand filter selection (reduced number of valid elements)
End With
The above code works as it should,
But if I selected any item (using drop down arrow on combobox itself) then I cannot list the combobox and I have to close the userform and open again.
If I commented this line of the code ,the issue disappeared ,But I lost the partial string match.
.List = Filter(dic.Keys, .Text, True, vbTextCompare)
And this my full code:
Private Sub ComboBox1_Change()
If ComboBox1.value <> "False" And ComboBox1.value <> "" Then
'Partial String Match
With Me.ComboBox1
.List = Filter(dic.Keys, .Text, True, vbTextCompare) 'The issue on this line
.DropDown 'Expand filter selection (reduced number of valid elements)
End With
End If
End Sub
As always, Thanks for all your interest to help.
CodePudding user response:
Please, try the next way:
- Create the next global variables on top of the form code module (in the declarations area):
Private dict As Scripting.Dictionary
Private noEvents As Boolean, boolNot As Boolean
- The used dictionary should be loaded prior to the following code event. Probably, in
UserFor_Initialize
event. I suppose that your code already does that.
Please copy the next event code instead of yours:
Private Sub ComboBox1_Change()
Dim chNo As Long, arr, chVar As String, mtch
If ComboBox1.Value <> "False" And ComboBox1.Value <> "" Then
If Not noEvents Then
With Me.ComboBox1
arr = filter(dict.Keys, .Text, True, vbTextCompare) 'place the filtered dict.keys in an array
mtch = Application.match(.Text, dict.Keys, 0) 'check if the combo value is A (whole) DICTIONARY KEY
'or PART OF IT
If IsError(mtch) Then 'if part of the key:
.List = arr 'load combo with filtered array
.DropDown 'Expand filter selection (reduced number of valid elements)
noEvents = True: boolNot = True 'make boolean variables True
Else 'if the whole key:
.List = dict.Keys: noEvents = False: boolNot = False 'load the dict keys and make bool vars False
End If
End With
Else
If Not boolNot Then
Me.ComboBox1.List = dict.Keys 'place the whole dict keys array
boolNot = True 'not letting next time do do the same
Else
boolNot = False 'reinitialize bool variable
End If
noEvents = False 'reinitialize bool variable
End If
Else
Me.cbE.List = dict.Keys 'when clearing combo value (nothing has been revealed by filterring, for instance)
End If
End Sub
Take care that I did not use dic
variable for the dictionary, I used dict
...
Please, send some feedback after testing it. I did not test it intensively, but I think it should do what (I understood) you need...