how loop if function in VBA

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 ...

Solution

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