Home > OS >  Issue with UserForm ComboBox (partial string match)
Issue with UserForm ComboBox (partial string match)

Time:11-21

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:

  1. 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
  1. 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...

  • Related