[Solved] How do I use a combobox and textbox on a userform in VBA to search and find data on the active Excel spreadsheet?

by


So her is a new Solution. You have to declare 3 Public Variables in the UserForm1 Modul. So you can give them Values while the USerForm is open and Find the Naxt Values when you click multiple Times on the Search Button.

'Public Variables 
    Public bolFirstSearch As Boolean
    Public rng As Excel.Range
    Public cellFound  As Excel.Range

Private Sub ComboBox1_Change()
    bolFirstSearch = False
End Sub

Private Sub CommandButton1_Click()
Dim strFindWhat As String
Dim intRowCB As Integer

On Error GoTo ErrorMessage
If UserForm1.bolFirstSearch = False Then

    strFindWhat = TextBox1.Text
    intRowCB = Cells.Find(What:=ComboBox1.value, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Column
    Set rng = Columns(intRowCB)
    rng.Select
        Set cellFound = rng.Find(What:=strFindWhat, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        cellFound.Select
Else

    Set cellFound = rng.FindNext(cellFound)
    cellFound.Select
End If
UserForm1.bolFirstSearch = True
Exit Sub
ErrorMessage:

 MsgBox ("The data you are searching for does not exist")
End Sub

Private Sub UserForm_Initialize()
    ComboBox1.List = Application.Transpose(Sheet1.Range("A1:D1").Value)
    bolFirstSearch = False
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    bolFirstSearch = False
End Sub

6

solved How do I use a combobox and textbox on a userform in VBA to search and find data on the active Excel spreadsheet?