How to handle multiple textboxes changes?

[DavidWas]

Hello all,

I have a UserForm with lots of textboxes (few dozens). I'm aiming at making it idiot- and mistake-proof so each textbox has some safety behaviours triggered by change events.

The simpliest and most common one is changing background colour upon value change. The code is like this:

Private Sub TextBox387_Change()
With TextBox387
If .Value = "" Then
.BackColor = RGB(255, 200, 200)
Else
.BackColor = RGB(200, 255, 200)
End If
End With
End Sub

Thing is, I have to copy and paste these lines for each individual textbox, changing the textbox number in each one. As the result, my code is of enormous size, hard to analyze and search.

Is there a way, to create some universal colour-changing (and other) instruction, that could be called out by value change event? I imagine it would require some variable, which would be a textbox number, so the function could "knew" which textbox is to change exactly.

My VBA knowledge is not very wide, I almost never use "modules" or "class modules" since I don't really understand their purpose but in this case I feel they might be useful. Any help?

 

[EXCEL MAX]

Hello DavidWas, welcome.
Here is one example how you can do that. It's not perfect but can be useful.
Copy this code to your UserForm code module.

Private Sub UserForm_Initialize()

    Application.OnTime Now + TimeValue("00:00:00"), "ControlChecking"
   
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    
    vClose = True
    
End Sub

Now insert standard module and copy this code there.

#If VBA7 Then ' Excel 2010 or later
Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal Milliseconds As LongPtr)
#Else ' Excel 2007 or earlier
Public Declare Sub Sleep Lib "kernel32" (ByVal Milliseconds As Long)
#End If

Public vClose As Boolean
Dim vCtrl As control

Sub ControlChecking()

     While vClose = False
        For Each vCtrl In UserForm1.Controls
            If TypeName(vCtrl) = "TextBox" Then
                If vCtrl.Value = "" Then
                   vCtrl.BackColor = RGB(255, 200, 200)
                Else
                   vCtrl.BackColor = RGB(200, 255, 200)
                End If
            End If
            Sleep 1
        Next
        DoEvents
    Wend
    
End Sub

 

[DavidWas]

Hey, that's very clever! Need some tweaking for my purpouses but that will do just fine.

Thank You