Help VBA code

[Prohock]

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)
Else
If Me.Option2 = True Then
Me.Text1.BackColor = RGB(216, 191, 216)
Else
If Me.Option3 = True Then
Me.Text1.BackColor = RGB(255, 214, 159)
Else
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)
Else
If Me.Option2 = True Then
Me.Text2.BackColor = RGB(216, 191, 216)
Else
If Me.Option3 = True Then
Me.Text2.BackColor = RGB(255, 214, 159)
Else
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)
Else
iColor = 15329774
End IF

'Loop through all the controls
'Assumptions:
'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

 

[Prohock]

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 AccessMonster.com]

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.

 

[ChrisO]

' 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