Looping through text boxes to apply conditional formatting

[wingnut74]

Hi.

I have been trying to loop through around 300 text boxes on a form to apply conditional formatting with little success. I have populated the text boxes with the following code:

TextBox18.Text = Application.VLookup(ComboBox1.Value, Range("C4:NK76"), 7, False)
TextBox19.Text = Application.VLookup(ComboBox1.Value, Range("C4:NK76"), 8, False)
TextBox20.Text = Application.VLookup(ComboBox1.Value, Range("C4:NK76"), 9, False)
TextBox21.Text = Application.VLookup(ComboBox1.Value, Range("C4:NK76"), 10, False)
TextBox22.Text = Application.VLookup(ComboBox1.Value, Range("C4:NK76"), 11, False)
[code/]

as so on up to the 300 text boxes.

I have then tried the following code to try and loop through all the text boxes to check for conditions and then change the back colour accordingly.

[code]
Sub BackColourLoop()


Dim ctl As Control
Dim ctlType As String


  ctlType = "Textbox"


  For Each ctl In frmHolidayDays.Controls
    
      If TypeName(ctl) = ctrlType Then
        If TextBox.Value = "FTJ" Then
                TextBox.BackColor = vbRed
        ElseIf TextBox.Value = "Sick" Then
                TextBox.BackColor = vbGreen
            
         End If
      End If
  Next ctrl


End Sub,

[code/]

I have tried other ways but nothing is working.

Your help would be much appreciated

 

[dmt32]

Hi,
welcome to forum.

There are some typo's in your code

Sub BackColourLoop()




Dim ctl As Control
Dim ctlType As String




ctlType = "Textbox"




For Each ctl In frmHolidayDays.Controls


If TypeName(ctl) = ctrlType Then
If TextBox.Value = "FTJ" Then
TextBox.BackColor = vbRed
ElseIf TextBox.Value = "Sick" Then
TextBox.BackColor = vbGreen


End If
End If
Next ctrl




End Sub

Variables shown in RED are not the same - you can avoid such an error by declaring Option Explicit at TOP of your code page.
Also, You are not referring to the Ctl object variable in your For Next Loop.





Try this update to your code & see if it does what you want

Option Explicit
Sub BackColourLoop()
    Dim ctl As Control
    Dim ctlType As String
    
    ctlType = "TextBox"
    
    For Each ctl In frmHolidayDays.Controls
        If TypeName(ctl) = ctlType Then
            ctl.BackColor = IIf(ctl.Value = "FTJ", vbRed, IIf(ctl.Value = "Sick", vbGreen, vbWhite))
        End If
    Next ctl
End Sub

As stated, Option Explicit goes at VERY TOP of the code page.

If this code is to reside in your Forms code page then you can replace the forms name (frmHolidayDays) with the Me keyword

For Each ctl In Me.Controls

Dave

 

[wingnut74]

Works perfectly, thank you so much for your help, really appreciate it Dave

 

[dmt32]

Works perfectly, thank you so much for your help, really appreciate it Dave

most welcome


Dave