Identifying control types on a form

[ALSav]

I have a form linked to a database that displays names and address of businesses. If the business has closed a text box 'txtDate_Closed' contains the date of closure. If the business is closed, I would like all text boxes on the form to have a yellow background. I do not want the background colour of labels to change so I need to be able to identify which controls on my form are textboxes. Here's my attempt (which doesn't work

Private Sub Status()
'If the business is closed, set the colour of the text boxes
'on the form to yellow otherwise set them to white
Dim MyCtrl As Control
Dim lngYellow As Long
Dim lngWhite As Long

lngYellow = RGB(255, 255, 0)
lngWhite = RGB(255, 255, 255)

If AdoBrowse.Recordset!Date_Closed <> &quot;&quot; Then
For Each MyCtrl In Controls

Private Sub PracticeStatus()
'If the practice is closed, set the colour of the text boxes
'on the main form to yellow otherwise set them to white
Dim MyCtrl As Control
Dim lngYellow As Long
Dim lngWhite As Long

lngYellow = RGB(255, 255, 0)
lngWhite = RGB(255, 255, 255)

If AdoBrowse.Recordset!Date_Closed <> &quot;&quot; Then
For Each MyCtrl In Controls
If TypeOf .Controls Is TextBox Then 'Problem here
MyCtrl.BackColor = lngYellow
End If
Next MyCtrl
Else
For Each MyCtrl In Controls
If TypeOf .Controls Is TextBox Then
MyCtrl.BackColor = lngWhite
End If
Next MyCtrl
End If

Any ideas??

 

[WildBillH]

Try something like this:

Dim llSub1 As Long
For llSub1 = 0 To Me.Controls.Count - 1
If UCase$(TypeName(.Controls(llSub1))) = &quot;TEXTBOX&quot; then
'
'<--- do whatever...
'
End IF
Next

Hope this helps -- WB

 

[WildBillH]

Sorry...I omitted the &quot;Me&quot; qualifier. The &quot;If&quot; statement should look like this:

If UCase$(TypeName(Me.Controls(llSub1))) = &quot;TEXTBOX&quot; then


Or, you could use With...EndWith.

 

[mikhailwaxman]

Try this:
For Each MyCtrl In Controls
If TypeOf MyCtrl Is TextBox Then
'Do something
End If
Next MyCtrl

 

[defektorx]

this is a good one, its code from a form, you can loop through all the textboxes in the me.controls collection. No need to determine the control type.

Option Explicit
Private txtBox As TextBox

Private Sub Form_Load()
For Each txtBox In Me.Controls

' me refers to the form
txtBox.BackColor = vbYellow

Next
End Sub