Dear Excel users, I have a problem to auto filter a predefined list in a combo-box. I have two comboboxes on a Userform - the first one calls up job numbers (Reg5) and the second one calls up activities (Reg6) that is related to whatever is selected in Reg5. Because the list in Reg 5 is massive I want to be able to filter the list as I type in a text or number. Any Suggestions? All help will be greatly appreciated!
Code:
Private Sub Reg5_Change()
Reg6.Clear
With Worksheets("Lists").Range("V3:V10000")
Set C = .Find(Reg5.Text, LookIn:=xlValues, LookAt:=xlWhole)
If Not C Is Nothing Then
firstAddress = C.Address
Do
Me.Reg6.AddItem (C.Offset(, 1).Text)
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address <> firstAddress
End If
End With
Set myRange = Worksheets("Lists").Range("H3:L1000")
Set f = myRange.Find(What:=Reg5.Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False) '<--| try and find combobox selected value
If f Is Nothing Then '<--| if not found ...
Reg8.Value = "" '<--| ... then clear textbox
Else '<--| ... otherwise...
Reg8.Value = f.Offset(, 4) '<--| ... fill it with proper value
End If
End Sub