CODEWITHSUNDEEP
vbaexceluserform
Michael Shore
Michael Shore

Reputation: 95

VBA Userform ListBox and TextBox

I have the follow sub to find a cell based on three criteria.

Private Sub FindEstimate_Click()

Dim i As Long

i = 5
Do
    If Cells(i, 1) = TextBox1 And Cells(i, 6) = ListBox1 And Cells(i, 9) =  ListBox2 Then
        Cells(i, 1).Select
    End If
    i = i + 1
Loop Until Cells(i, 1) = TextBox1 And Cells(i, 6) = ListBox1 And Cells(i, 9) = ListBox2

End Sub

It simply does not work, and I suspect it has something to do with the Loop Until statement.

Upvotes: 0

Views: 1135

Answers (1)

Shai Rado
Shai Rado

Reputation: 33692

You better of using the Find function, and loop through all Find results in Column "A" (in case there are multiple matches with TextBox1.Value), until you are able to find also a match with ListBox1.Value and ListBox2.Value.

For that you will use a Do <-> Loop While Not FindRng Is Nothing And FindRng.Address <> FirstAddres loop.

Code

Option Explicit

Private Sub FindEstimate_Click()

Dim Rng As Range
Dim FindRng As Range
Dim FirstRng As Range
Dim FirstAddress As String


Set Rng = Range("A5:A" & Cells(Rows.Count, "A").End(xlUp).Row)

With Rng
    Set FindRng = .Find(what:=Me.TextBox1.Value, LookIn:=xlValues, _
                        lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext)

    If Not FindRng Is Nothing Then ' find was successful
       ' Set FirstRng = FindRng
        FirstAddress = FindRng.Address

        Do
            If FindRng.Offset(, 5).Value = Me.ListBox1.Value And FindRng.Offset(, 8).Value = Me.ListBox2.Value Then
                FindRng.Select ' <-- not sure why you need to select it
                Exit Do
            End If
            Set FindRng = .FindNext(FindRng)
        Loop While Not FindRng Is Nothing And FindRng.Address <> FirstAddress

    Else ' Find faild to find the value in TextBox1
        MsgBox "Unable to find " & Me.TextBox1.Value & " at column A"
    End If
End With

End Sub

Upvotes: 1

Related Questions