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.

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.

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.

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.

Two points about your code (even though it appears not to be what the OP
wanted)... first, you should probably have an Else block in your ChangeColor
subroutine to change the colors back to the default fore and back colors if
the text in the box is changed from "actual" and "projected" to something
else (otherwise they remain colored); and second, because this is a UserForm
and you are calling your ChangeColor subroutine from an event, you don't
have to pass the TextBox as an argument to the subroutine, you can just
refer to it through the UserForm's ActiveControl object. Here is what I am
thinking...

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

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

Private Sub ChangeColor()
With UserForm1.ActiveControl
If .Text = "actual" Then
.BackColor = &H8000&
.ForeColor = &HFFFFFF
ElseIf .Text = "projected" Then
.BackColor = &H8000&
.ForeColor = &HFFFFFF
Else
.BackColor = &H80000005
.ForeColor = &H80000008
End If
End With
End Sub

--
Rick (MVP - Excel)


"Jacob Skaria" <(E-Mail Removed)> wrote in message
news:FB51DD49-CC46-4E6A-A777-(E-Mail Removed)...
> 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.

The code Jacob gave you makes the color change automatic (see my follow up
comments to him though)... change the text, move out of the TextBox and the
color changes right then and there. To do that, you need event code and
event code means you have to have one for each control you want it to apply
to. I don't see what your resistance is to just putting the 28 Exit event
procedures in the code window... just use the modification I posted back to
Jacob and all your 28 Exit events have to have in them is the single
subroutine name ChangeColor... that's it... do it once and you are done. If
you are worried about future code maintenance, you don't have to be... all
active code is in the single ChangeColor subroutine... any change you make
there is used by all 28 TextBoxes without you having to ever touch their
Exit event procedures. If you go with the For Each routine Jacob just
posted, the changing of the color will not be automatic.. you will have to
manually kick off the code yourself (perhaps by pushing a button)... forget
to do it and the color scheme will be wrong. I think Jacob's original event
procedure method is the better way to go.

--
Rick (MVP - Excel)


"Ayo" <(E-Mail Removed)> wrote in message
news:3A4A8CB3-FECB-41F3-AD6D-(E-Mail Removed)...
> 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.

Thanks Rick for the suggestions..

Since the OP seems to be less worried with the current code its true that i
didnt take the extra effort to review the code posted

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


"Rick Rothstein" wrote:

> Two points about your code (even though it appears not to be what the OP
> wanted)... first, you should probably have an Else block in your ChangeColor
> subroutine to change the colors back to the default fore and back colors if
> the text in the box is changed from "actual" and "projected" to something
> else (otherwise they remain colored); and second, because this is a UserForm
> and you are calling your ChangeColor subroutine from an event, you don't
> have to pass the TextBox as an argument to the subroutine, you can just
> refer to it through the UserForm's ActiveControl object. Here is what I am
> thinking...
>
> Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
> ChangeColor
> End Sub
>
> Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
> ChangeColor
> End Sub
>
> Private Sub ChangeColor()
> With UserForm1.ActiveControl
> If .Text = "actual" Then
> .BackColor = &H8000&
> .ForeColor = &HFFFFFF
> ElseIf .Text = "projected" Then
> .BackColor = &H8000&
> .ForeColor = &HFFFFFF
> Else
> .BackColor = &H80000005
> .ForeColor = &H80000008
> End If
> End With
> End Sub
>
> --
> Rick (MVP - Excel)
>
>
> "Jacob Skaria" <(E-Mail Removed)> wrote in message
> news:FB51DD49-CC46-4E6A-A777-(E-Mail Removed)...
> > 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.
>
>

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
<(E-Mail Removed)> 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.