Changing colour of text box when data is added.

Try this...

Add a new userform and 2 textboxes. Paste the following in the userform code module:

Option Explicit
Dim TBOXES() As New Class1    '// **** NOTE - Edited. Make sure you have this version ****
Private Sub UserForm_Initialize()

    Dim Counter As Integer
    Dim Obj As Control
    
    For Each Obj In Me.Controls
        If TypeOf Obj Is MSForms.TextBox Then
            Counter = Counter + 1
            ReDim Preserve TBOXES(1 To Counter)
            Set TBOXES(Counter).AssignTB = Obj
        End If
        
    Next Obj
    
    Set Obj = Nothing
    
End Sub

Add a new CLASS module. Paste the following to the class:

Option Explicit

Public WithEvents TB As MSForms.TextBox

Public Property Set AssignTB(t As MSForms.TextBox)
    
    Set TB = t
    
    '// force change of colour
    TB_Change
    
End Property


Private Sub TB_Change()

    TB.BackColor = IIf(TB.Text = vbNullString, &HC0FFFF, &H80000005)

End Sub

Private Sub TB_MouseDown(ByVal Button As Integer, _
    ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    
    With TB
         ' Do something
    End With
    
End Sub

Leave all names as the default, the Class should be named Class1. Hopefully you do not have any other classes (Doubtful ) but it's easy enough to change.

Try running that (the userform, not your complete project) and see if you can understand what's going on... The key to this is the declaration using WITHEVENTS in the Class and the array of classes in the userform.

After running once, add another textbox or 2. No coding changes will be needed for the new textboxes to respond to changes. (Hopefully!)

There are two ways to do this.

The simplest is to use a subroutine

Untested Code:

Sub textbox1_change()
TextChange (1)
End Sub
Sub textbox2_change()
TextChange (2)
End Sub

Sub TextChange(TbNo As Integer)

If Len(Me.Controls("Textbox" & TbNo).Text) = "" Then
Me.Controls("Textbox" & TbNo).BackColor = vbWhite
Else
Me.Controls("Textbox" & TbNo).BackColor = vbYellow
End If
End Sub

You will need 100 of the textbox change sub routines.

But these can be created in excel using another macro and then cut and pasted into visual basic.

Sub Macro1()

    Range("A1").FormulaR1C1 = _
        "=""Sub Textbox"" & CEILING( ROW()/3,1)& ""_Change()"""
    Range("A2").FormulaR1C1 = "TextChange"
    Range("A3").FormulaR1C1 = "End Sub"
    Range("A1:A3").Copy Destination:=Range("A1:A300")
    Range("A1:A300").Value = Range("A1:A300").Value
End Sub

___________________________________________________________________________________________________________________________________________________________

The Second is to create a new cllass of object.

This shows you how:

http://www.ozgrid.com/forum/showthread.php?t=141695

Use a class to handle the change event for numerous textboxes.