You can do it with a class module. For each text box on the form,
change the Tag property to the text that, if entered into the text
box, will trigger the color change. Using your code as an example,
you'd set the Tag of txtbx2 to "actual".

Then, create a new class module (Insert menu in VBA, Class Module),
name it CTextBox, and paste in the following code:


Option Explicit

Public WithEvents TBX As MSForms.TextBox

Private Sub TBX_Change()
Dim Tag As String
Tag = CallByName(TBX, "Tag", VbGet)
If Tag < vbNullString Then
If StrComp(TBX.Text, Tag, vbTextCompare) = 0 Then
TBX.BackColor = RGB(255, 0, 0)
TBX.ForeColor = RGB(0, 255, 0)
Else
TBX.BackColor = RGB(0, 0, 255)
TBX.ForeColor = RGB(0, 255, 0)
End If
End If
End Sub


Then, in the userform's code module, paste in

Private pColl As Collection

Private Sub UserForm_Initialize()

Dim C As MSForms.Control
Dim CTBX As CTextBox
Set pColl = New Collection
For Each C In Me.Controls
If TypeOf C Is MSForms.TextBox Then
Set CTBX = New CTextBox
Set CTBX.TBX = C
pColl.Add CTBX
End If
Next C

End Sub

With this code in place, when a TextBox is changed, the Change event
within the instance of CTextBox for that text box will be called, and
the current value of the text box will be tested against the value of
the Tag property. If they are equal, the colors are changed to

TBX.BackColor = RGB(255, 0, 0)
TBX.ForeColor = RGB(0, 255, 0)

If the text in the text box doesn't equal Tag, then the colors are set
as

TBX.BackColor = RGB(0, 0, 255)
TBX.ForeColor = RGB(0, 255, 0)

Change the actual color value to what you need.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Mon, 24 Aug 2009 13:08:10 -0700, Ayo
wrote:

I have a UserForm that contains about 28 TextBoxes. I need to change the Back
and Fore color of the textboxes based on the values inside each textbox.
Below is an example of what I am looking to accomplish:
Private Sub txtbx2_Change()
If txtbx2.Text = "actual" Then
ctl.BackColor = &H8000&
ctl.ForeColor = &HFFFFFF
ElseIf txtbx2.Text = "projected" Then
ctl.BackColor = &H8000&
ctl.ForeColor = &HFFFFFF
End If
End Sub

but I don't want to have to write this code for each and every textbox on
the form. Is there a way to do this within one subroutine?
I am looking for one subrutine that would automatically update the textboxes
once the value in it is change and focus is set to another control on the
form.