VBA Loop through textbox controls in userform

Question

I have looked through numerous posts on looping through UserForm Controls but cant seem to adjust the code i have found for my needs and need some help.

Scenario I am trying to figure out:

I have 44 text boxes on a userform whose names all start with "ch" example "chTextBox1"
When the userform activates I need to loop through all of the text boxes that start with "ch" and change the background color of those textboxes to a color based on the interior color of a cell

Below is the code that I have been messing around with and I either end up in an infinite loop or I get

Error 424

Private Sub UserForm_Activate()
    Dim wb As Workbook
    Dim wsRR As Worksheet
    Dim bColor As Range
    Dim c As Control
    Dim y As String

    Set wb = Application.ThisWorkbook
    Set wsRR = wb.Sheets("RiskRating")
    Set bColor = wsRR.Range("C3")   

    For Each c In JHKey.Controls
        If TypeName(c) = "TextBox" Then
            y = Left(c, 2)
        End If
        If y = "ch" Then
            c.BackColor = bColor.Interior.Color
        End If
    Next c
End Sub

Hint: the value of y is carried between iterations, regardless of the outcome of the first If block. — Mathieu Guindon
– Mathieu Guindon, Commented Aug 8, 2018 at 16:13

score 3 · Accepted Answer · 2018-08-08 16:12:49Z

3

Try placing the If statement testing for "ch" within the If statement testing for "TextBox". Also, you should specify the Name property for the control when checking for its name, otherwise it defaults to its Value property. Also, as an aside, I would suggest replacing JHKey with the keyword Me, which refers to the userform itself regardless of its name.

Private Sub UserForm_Activate()
    Dim wb As Workbook
    Dim wsRR As Worksheet
    Dim bColor As Range
    Dim c As Control
    Dim y As String

    Set wb = Application.ThisWorkbook
    Set wsRR = wb.Sheets("RiskRating")
    Set bColor = wsRR.Range("C3")

    For Each c In Me.Controls
        If TypeName(c) = "TextBox" Then
            y = Left(c.Name, 2)
            If y = "ch" Then
                c.BackColor = bColor.Interior.Color
            End If
        End If
    Next c
End Sub

edited Aug 8, 2018 at 16:12

answered Aug 8, 2018 at 16:05

user6585761

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

2 Comments

Mathieu Guindon Over a year ago

Not only Me refers to the userform instance, it refers to the currently running instance, whereas using the form's name refers to the default instance, which is the running instance only if you brought up that form using the default instance - IOW running an instance of the form e.g. With New MyForm : .Show : End With, breaks.

Zack E Over a year ago

Thank you so much both of you for the useful information and help. The edit you made to the code worked like a charm and did exactly what i needed it to do.

Collectives™ on Stack Overflow

VBA Loop through textbox controls in userform

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related