Multiple IF statements with textbox string comparison

Question

My beginners code [which works perfectly well] uses multiple IF statements. My question is [before I go any further because I will have lots of textboxes [txtbx#] is there any downside of coding this way ?

The user will be typing 40-50 short strings into textboxes while someone reads them to him then clicks a button to fill color all the matches in the worksheet column;

Private Sub CommandButton1_Click()
    Dim i As Integer
    Dim AbbNum As String
    Dim AbbNum2 As String
    Dim AbbNum3 As String
    Dim AbbNum4 As String
    Dim AbbNum5 As String
    Dim c As Integer
    Dim MySheet As Worksheet
    Set MySheet = Sheets(2)
    c = MySheet.Cells(Rows.Count, 2).End(xlUp).Row
    For i = 2 To c
         AbbNum = txtbxONE.Value
            If Cells(i, 1).Value = AbbNum Then
               Cells(i, 1).Interior.ColorIndex = 6
            End If
         AbbNum2 = txtbxTWO.Value
            If Cells(i, 1).Value = AbbNum2 Then
               Cells(i, 1).Interior.ColorIndex = 6
            End If
         AbbNum3 = txtbxTHREE.Value
            If Cells(i, 1).Value = AbbNum3 Then
               Cells(i, 1).Interior.ColorIndex = 6
            End If
         AbbNum4 = txtbxFOUR.Value
            If Cells(i, 1).Value = AbbNum4 Then
               Cells(i, 1).Interior.ColorIndex = 6
            End If
         AbbNum5 = txtbxFIVE.Value
            If Cells(i, 1).Value = AbbNum5 Then
               Cells(i, 1).Interior.ColorIndex = 6
            End If

    Next i

End Sub

at each for loop the value of a cell will be compared against all the checkbox contents, is it intentional? — Máté Juhász
– Máté Juhász, Commented May 26, 2015 at 20:33

Tim Williams · Accepted Answer · 2015-05-26 22:21:37Z

3

If you name your textboxes "txtbx1", "txtbx2", etc you can do something like this:

Private Sub CommandButton1_Click()

    Const NUM_TEXTBOXES as Long = 10 'for example....

    Dim i As Long
    Dim AbbNum As String, f As Range, rngSrch As Range

    Set rngSrch = Sheets(2).Range("A:A")

    For i = 1 To NUM_TEXTBOXES  

         AbbNum = Me.Controls("txtbx" & i).Value

         If Len(AbbNum) > 0 Then
             'EDIT: fixed typo in next line
             Set f = rngSrch.Find(AbbNum, lookin:=xlvalues, lookat:=xlWhole)
             If Not f Is nothing then f.Interior.ColorIndex = 6
         End If

    Next i

End Sub

edited May 26, 2015 at 22:21

answered May 26, 2015 at 20:34

Tim Williams

169k8 gold badges104 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Robert Builder Over a year ago

Thanks Tim. Food for thought there. I changed the textbox names to txtbx1 txtbx2 and NUM_TEXTBOXES as Long = 5 (amount of textboxes) but the code didn't color any cells ?

Tim Williams Over a year ago

Sorry - fixed a typo in my answer: AddNum >> AbbNum

Robert Builder Over a year ago

The typo fixed it. Massively impressed. Learning curve for me such a small code block instead of multiple IFs. Thank you very much indeed Tim

Tim Williams Over a year ago

No problem - one benefit you get from writing lots of VBA is writing much less VBA...

Robert Builder Over a year ago

I can also see that I can add as many textboxes as I like & I only need to change the NUM_TEXTBOXES as Long = #value. That's great !

Collectives™ on Stack Overflow

Multiple IF statements with textbox string comparison

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related