VBA Filter combobox on userform with search suggestions

[MarcelCleijsen]

I have created an excel userform with some comboboxes, attached a document with the problem but stripped down to the basic problem.


I figured out how i can filter/autoupdate my comboboxes and decrease the input list while i start typing in the list. However as soon as i select an item i get the following error: "Unable to get the CurrentRegion property of the Range class". I have tried many different solutions to get rid of the problem. But still can not figure out this final error. Even when I try to use "Range" instead of "CurrentRegion" I get these errors.


Since i want to apply this feature to all comboboxes on my userforms i have put everything in a class event.


I have already found some stuff online but these were basically comboboxes on a worksheet. I like to actively update the data on a userform. I think I have applied all stuff I found online in the code which I now have, but still need to solve this final issue. Until now all my solutions crashed.


this is my example file


Thank you very much!


Best Regards, Marcel

Option Explicit


Public WithEvents myCBox As msForms.ComboBox
Dim data As Range


Private Sub myCBox_Change()
Set data = filter.Cells(1, 1).CurrentRegion
' filter.Activate
With myCBox
   .DropDown


    ' FILTER
    list.Cells(1, 1).CurrentRegion.AutoFilter Field:=1, Criteria1:="*" & .Value & "*"


    ' COPY
    filter.Cells.Clear
    list.Cells(1, 1).CurrentRegion.Copy Destination:=filter.Cells(1, 1)


    Set data = filter.Cells(1, 1).CurrentRegion
    num_rows_2 = data.Rows.Count


    ' UPDATE COMBOBOX
    .RowSource = "filter!A2:A" & num_rows_2


End With
End Sub

 

[MarcelCleijsen]

Hi Jerry,

Sorry for the late response, somehow i missed your reply.

I will check your code immediately.!!

 

[MarcelCleijsen]

Hi Jerry,

I just dove into it. It works like a charm. Thank you so much for your help and effort. Really appreciated!!!

Best Regards, Marcel

 

[Hebsm]

Hi MarcelCleijsen,

Please help me, I want to do the same.
Can you send me your file?

Thank you!!!!

 

[FaizanRoshan]

Hi Jerry Sullivan,
I have faced same problem in my user-form, However i have more then 50 combo-box with different list range. Please help to make all as a searchable combo-box.

 

[asidorov]

That's terrific.

Would you mind helping me understand how to adjust this code for one of the following options:
a) Instead of creating new comboboxes, i'd try to adjust the properties of an existing one, which should still be able to filter. Probably there is a way how to pass the new class properties to the existing box?
b) Read out the input from one of the created comboboxes (writing of the entry into a cell of the worksheet). I just cannot find the instance where this happens.

Any suggestion will be highly appreciated. Thank you in advance.

 

[din2din]

@Jerry Sullivan: Thanks for the above code. It works beautifully. How would I go about pasting the selected information from the ComboBox into a particular cell in excel? Usually I would use something like ws.cells(i,1).value = ComboBox1.value. But I am unable to see the ComboBox properties to get the ComboBox name. Below is my code:

Option Explicit

'--in UserForm Code Module "NewRecord"

'******UserForm Variable Declarations******************************
Private mbStopClassEvents As Boolean
Private mbUserCancel As Boolean
Private mcolCBoxes As New Collection


'******UserForm Property Procedures********************************
Public Property Get OK() As Boolean
 OK = Not mbUserCancel
End Property


'******UserForm Event Procedures***********************************
Private Sub UserForm_Initialize()
 mbStopClassEvents = True
 AddCBoxes
 mbStopClassEvents = False
End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
 If CloseMode <> vbFormCode Then
   Cancel = True
   cmdCancel_Click
 End If
End Sub


'******Control Event Procedures*********************************
Private Sub cmdOK_Click()
 mbUserCancel = False
 
 Dim rw As Integer
 Dim ws As Worksheet
 Set ws = Worksheets("Orders")
 
 rw = Cells(Columns.Count, "A").End(xlUp).Row + 1
 MsgBox "Stored!", vbOKOnly
 
 ws.Cells(rw, 1).Value = Me.TextBox1.Value
 
 ws.Cells(rw, 6).Value = Me.TextBox2.Value
 
 Me.Hide
End Sub


Private Sub cmdCancel_Click()
 mbUserCancel = True
 Me.Hide
End Sub


'******Userform Private Procedures*********************************
Public Function GetItemList(Optional sListName As String) As Variant
 '--get list of all items from workbook
 '--sListName argument only needed if VB Project uses this function
 '    to read more than one list data source
 
 GetItemList = vGetRecord()
 
End Function

'******Userform Private Procedures*********************************
Private Sub AddCBoxes()
'--adds comboboxes to userform to fill specified area
'--comboboxes use CComboFilter class to filter down list based
'    on user's text entries

 Dim cbo As Control
 Dim clsCBox As CCOmboFilter
 Dim lCBoxLastTop As Long
 Dim lItem As Long, lCBoxTop As Long
 
 Const lCBOX_FIRST_TOP As Long = 42
 Const lCBOX_HEIGHT As Long = 18
 Const lHEIGHT_BTWN_CBOXES As Long = 40
 Const lCBOX_LEFT As Long = 84
 
 'min space. allows for buttons
 lCBoxLastTop = CLng(Me.Height) - 50
 
 lCBoxTop = lCBOX_FIRST_TOP
 
 '--create combobox1
 If lCBoxTop < lCBoxLastTop Then
   lItem = lItem + 1
   Set cbo = Me.Controls.Add("Forms.ComboBox.1", _
      "cboRecord_" & lItem, True)
   With cbo
      .Top = lCBoxTop
      .Left = lCBOX_LEFT
      .Height = lCBOX_HEIGHT
      .Width = Me.Width - 2.46 * lCBOX_LEFT
      .Font.Size = 11
      .Font.Name = "Calibri"
      .MatchEntry = 2
   End With
  
   '--pass to new ComboFilter Class instances
   Set clsCBox = New CCOmboFilter
   With clsCBox
      Set .BaseComboBox = cbo
      Set .Parent = Me
   End With

   mcolCBoxes.Add Item:=clsCBox
   lCBoxTop = lCBoxTop + lCBOX_HEIGHT + lHEIGHT_BTWN_CBOXES
 End If

End Sub

 

[vbaBeginner94]

Hi, is it possible to use this Combobox-Filter for 2 Columns? I'm looking for an example with a 2-Column-Combobox (Range "A:B") that filters the Data based in Column 1.