how loop if function in VBA

Question

How to repeat the if function in a vba code Hello, all I create a command line using if on a form. But I have trouble writing it over and over again. Is there a proper way to simplify this writing?

If UserForm1.checkbox15.Value = True Then
UserForm2.textbox1.enable = True
UserForm2.textbox1.BackColor = RGB(200, 200, 200)
Else
UserForm2.textbox1.enable = True
UserForm2.textbox1.BackColor = RGB(255, 255, 255)
End If

If UserForm1.checkbox16.Value = True Then
UserForm2.textbox2.enable = True
UserForm2.textbox2.BackColor = RGB(200, 200, 200)
Else
UserForm2.textbox2.enable = True
UserForm2.textbox2.BackColor = RGB(255, 255, 255)
End If

If UserForm1.checkbox17.Value = True Then
UserForm2.textbox3.enable = True
UserForm2.textbox3.BackColor = RGB(200, 200, 200)
Else
UserForm2.textbox3.enable = True
UserForm2.textbox3.BackColor = RGB(255, 255, 255)
End If

If UserForm1.checkbox18.Value = True Then
UserForm2.textbox4.enable = True
UserForm2.textbox4.BackColor = RGB(200, 200, 200)
Else
UserForm2.textbox4.enable = True
UserForm2.textbox4.BackColor = RGB(255, 255, 255)
End If

.... .... .... to textbox660 ...

Answer 1

Define your color long values as Const outside of the subroutines, at the top of the module.

Public Const bgColorTrue As Long = 13158600  '## RGB(200,200,200)
Public Const bgColorFalse As Long = 16777215 '## RGB(255,255,255)

Then you can do something like this, which is somewhat more simplified, but still requires enumerating all of the pair of TextBox + CheckBox.

UserForm2.textbox1.BackColor = IIF(UserForm1.checkbox15.Value = True, bgColorTrue, bgColorFalse)
UserForm2.textbox2.BackColor = IIF(UserForm1.checkbox16.Value = True, bgColorTrue, bgColorFalse)
UserForm2.textbox3.BackColor = IIF(UserForm1.checkbox17.Value = True, bgColorTrue, bgColorFalse)
UserForm2.textbox4.BackColor = IIF(UserForm1.checkbox18.Value = True, bgColorTrue, bgColorFalse)
UserForm2.textbox5.BackColor = IIF(UserForm1.checkbox19.Value = True, bgColorTrue, bgColorFalse)
' etc...

Or (assuming the arithmetic relationship between the indexes of the checkboxes and textboxes is constant), you can do a loop and create a subroutine to identify which TextBox pairs with which CheckBox:

Dim cb as MSForms.CheckBox, ctrl as Control
For Each ctrl in UserForm1.Controls
    If TypeName(cb) = "CheckBox" Then
        Call UpdateForm2(cb, UserForm2)
    End If
Next

Using a sub like:

Sub UpdateForm2(ByRef cb as MSForms.CheckBox, byRef Uf as UserForm)
    Dim tb as MSForms.TextBox
    Dim iCB as Long, iTB as Long
    '## Get the index of this CheckBox name:
    iCB = CLng(Replace(cb.Name, "checkbox", vbNullString))
    '## Compute the index of corresponding TextBox name
    iTB = iCB - 14
    '## Handle the TextBox on the other UserForm
    Set tb = Uf.Controls("TextBox" & iTB)
    tb.BackColor = IIF(cb.Value = True, bgColorTrue, bgColorFalse)
End Sub