Reduce code for Change event for multiple TextBoxes and ComboBoxes

[AndyEd]

I have the following code used to check if there is a value displayed in TextBoxes and ComboBoxes. The code is fired via ComboBox change event.

    With Me.tbxFileOpenDate
        If .Value = vbNullString Then
            .BackColor = RGB(255, 242, 204)
        Else
            .BackColor = vbWhite
        End If
    End With

The issue is that I have in excess of 50 such objects on the UserForm. Is there way to apply the code to all the objects without having to code for them individually?

Thank you in advance.

 

[NdNoviceHlp]

Hi AndyEd. This seems like it might work if your controls were generically named (ie. Textbox1, Textbox2, etc.) Don't think it will work for named controls? Maybe the loop mechanics will help you. Dave

Sub test()
Dim ctl As Control
For Each ctl In Userform1.Controls
If InStr(ctl.Name, "TextBox") = True Or _
InStr(ctl.Name, "ComboBox") = True Then
If ctl.Value = vbNullString Then
    ctl.BackColor = RGB(255, 242, 204)
Else
    ctl.BackColor = vbWhite
End If
End If
Next ctl
End Sub

 

[AndyEd]

Another user provided the following cleanup whilst looking at another issue I had.

Private Sub cboSelectFile_Change()
Call EmptyCheck(Me.tbxFileOpenDate)
:
:
End Sub

Private Sub EmptyCheck(ByRef Ctrl As Object)    'TextBox or ComboBox
    With Ctrl
        If .Value = vbNullString Then
            .BackColor = RGB(255, 242, 204)
        Else
            .BackColor = vbWhite
        End If
    End With
End Sub

 

[NdNoviceHlp]

Thanks for posting your outcome. Dave