Below is a single procedure which will lookinto all textboxes in the form.

Since your requirement is to "update the textboxes once the value in it is
change and focus is set to another control on the form." you will have to
call a procedure from each text box exit event....In that case when you call
this from each exit event then there is no point looping through all
textboxes...and hence my earlier post...but a single procedure with the
looping process would look like the below

Dim Ctrl As MSForms.Control
For Each Ctrl In UserForm1.Controls
If TypeOf Ctrl Is MSForms.TextBox Then

If Ctrl.Object.Text = "actual" Then
Ctrl.Object.BackColor = &H8000&
Ctrl.Object.ForeColor = &HFFFFFF
ElseIf Ctrl.Object.Text = "projected" Then
Ctrl.Object.BackColor = &H8000&
Ctrl.Object.ForeColor = &HFFFFFF
End If

End If
Next Ctrl

If this post helps click Yes
---------------
Jacob Skaria


"Ayo" wrote:

This still involves writing 28 TextBox1_Exit subroutine. I am looking for an
option that only involves one For Each .... Next sub.

"Jacob Skaria" wrote:

Try the below...

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
ChangeColor Me.TextBox1
End Sub

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
ChangeColor Me.TextBox2
End Sub

Private Sub ChangeColor(txtTemp As MSForms.TextBox)
If txtTemp.Text = "actual" Then
txtTemp.BackColor = &H8000&
txtTemp.ForeColor = &HFFFFFF
ElseIf txtTemp.Text = "projected" Then
txtTemp.BackColor = &H8000&
txtTemp.ForeColor = &HFFFFFF
End If
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"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.