Help VBA code



I have a series of text boxes that change color depending on what option
button is selected. How can I condense the code below so that it could work
with hundreds of text boxes i.e. text99? I am new to VBA and I think that I
have to use variables?

Private Sub Text1_Click()
If Me.Option1 = True Then
Me.Text1.BackColor = RGB(164, 211, 238)
If Me.Option2 = True Then
Me.Text1.BackColor = RGB(216, 191, 216)
If Me.Option3 = True Then
Me.Text1.BackColor = RGB(255, 214, 159)
Me.Text1.BackColor = 15329774
End If
End If
End If

End Sub

Private Sub Text2_Click()
If Me.Option1 = True Then
Me.Text2.BackColor = RGB(164, 211, 238)
If Me.Option2 = True Then
Me.Text2.BackColor = RGB(216, 191, 216)
If Me.Option3 = True Then
Me.Text2.BackColor = RGB(255, 214, 159)
Me.Text2.BackColor = 15329774
End If
End If
End If

End Sub

David C. Holley

What is the specific need to have 'hundreds' of text boxes? What's the
bigger picture?

John Spencer

Function fChangeColor()
Dim i as Long
Dim iColor as Long

'Get the color
IF Me.Option1 = True Then
iColor = RGB(164, 211, 238)
ElseIf Me.Option2 = True Then
iColor = RGB(216, 191, 216)
ElseIf Me.Option3 = True then
iColor = RGB(255, 214, 159)
iColor = 15329774
End IF

'Loop through all the controls
'All the controls are named "text" and followed by a number
'There are are 99 controls from 1 to 99
For i = 1 to 99
Me("Text" & i).Backcolor = iColor
Next i

End Function

If the assumptions are wrong then you could use variation on the code. I
guessed that you wanted to change all the controls at one time and all to the
same colors. I wonder why you are using the click event and what your purpose
is in doing this.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County


The purpose is a calendar and I need to count dates based on the type of
color assigned.

David C. Holley

Can you describe what exactly you're doing? What's the calendar for? The
comment about 'hundreds' of text boxes is a huge red flag that you may not
be approaching this from the best standpoint design-wise. Not to mention
that Access can fully integrate with Outlook.

David C. Holley

I might also point out that even if you're going to stricktly use Access, it
is more customary to assign a status to a particular calendar item and then
use the category to change the color of the item. If you need a count of
items under a particular category, its just a matter of running a query on
the data.

Linq Adams via

If you actually only have 3 options and a default, you could go into Design
View, select all of the textboxes, then goto Format - Conditional Format and
set your conditions/colors.


' I think the Option selection might
' be better if it's an Option Group.

Option Explicit
Option Compare Text

Private Sub Form_Open(Cancel As Integer)
Dim intSuffix As Integer
Dim strCtlName As String

' For contiguous text boxes named Text1 to Text99
For intSuffix = 1 To 99
strCtlName = "Text" & CStr(intSuffix)
Me(strCtlName).BackStyle = 1
Me(strCtlName).OnClick = "=HandleClick('" & strCtlName & "')"
Next intSuffix

End Sub

Private Function HandleClick(ByVal strCtlName As String)

Select Case Me.OptionGroupFrame
Case 1
Me(strCtlName).BackColor = RGB(164, 211, 238)
Case 2
Me(strCtlName).BackColor = RGB(216, 191, 216)
Case 3
Me(strCtlName).BackColor = RGB(255, 214, 159)
Case Else
Me(strCtlName).BackColor = 15329774
End Select

End Function

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question