Multiple Colour changes on User Form

[Trefor]

On a userform I have a "Frame" containing a bunch of text boxes. If the text
box contains the word "Completed" it changes the text box to Black on Green
other it set the text bov to Red on Red. Below is the Module code I am using,
as it stands I am going to have to code this for each and every text box in
the frame. Is it possible to run a loop of some sort across all text boxes in
the one frame?


If Menu.StatusUnisysComp.Value = "Completed" Then
Menu.StatusUnisysComp.ForeColor = 0
Menu.StatusUnisysComp.BackColor = RGB(0, 255, 0)
Else
Menu.StatusUnisysComp.ForeColor = RGB(255, 0, 0)
Menu.StatusUnisysComp.BackColor = RGB(255, 0, 0)
End If

If Menu.StatusNSR.Value = "Completed" Then
Menu.StatusNSR.ForeColor = 0
Menu.StatusNSR.BackColor = RGB(0, 255, 0)
Else
Menu.StatusNSR.ForeColor = RGB(255, 0, 0)
Menu.StatusNSR.BackColor = RGB(255, 0, 0)
End If

--
Trefor

[Jim Cone]

Trefor,
'----------------
Private Sub Frame1_Click()
Call FixThemColors(Me.Frame1.ActiveControl)
End Sub

Sub FixThemColors(ByRef objBox As MSForms.Control)
If TypeName(objBox) = "TextBox" Then
If objBox.Value = "Completed" Then
objBox.ForeColor = 0
objBox.BackColor = RGB(0, 255, 0)
Else
objBox.ForeColor = RGB(255, 0, 0)
objBox.BackColor = RGB(255, 0, 0)
End If
End If
End Sub
'------------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Trefor" <[email protected]> wrote in message...
On a userform I have a "Frame" containing a bunch of text boxes. If the text
box contains the word "Completed" it changes the text box to Black on Green
other it set the text bov to Red on Red. Below is the Module code I am using,
as it stands I am going to have to code this for each and every text box in
the frame. Is it possible to run a loop of some sort across all text boxes in
the one frame?


If Menu.StatusUnisysComp.Value = "Completed" Then
Menu.StatusUnisysComp.ForeColor = 0
Menu.StatusUnisysComp.BackColor = RGB(0, 255, 0)
Else
Menu.StatusUnisysComp.ForeColor = RGB(255, 0, 0)
Menu.StatusUnisysComp.BackColor = RGB(255, 0, 0)
End If

If Menu.StatusNSR.Value = "Completed" Then
Menu.StatusNSR.ForeColor = 0
Menu.StatusNSR.BackColor = RGB(0, 255, 0)
Else
Menu.StatusNSR.ForeColor = RGB(255, 0, 0)
Menu.StatusNSR.BackColor = RGB(255, 0, 0)
End If

--
Trefor

[Glen Mettler]

Why not use Conditional Formating?

Glen

"Trefor" <[email protected]> wrote in message
news:[email protected]...
> On a userform I have a "Frame" containing a bunch of text boxes. If the
> text
> box contains the word "Completed" it changes the text box to Black on
> Green
> other it set the text bov to Red on Red. Below is the Module code I am
> using,
> as it stands I am going to have to code this for each and every text box
> in
> the frame. Is it possible to run a loop of some sort across all text boxes
> in
> the one frame?
>
>
> If Menu.StatusUnisysComp.Value = "Completed" Then
> Menu.StatusUnisysComp.ForeColor = 0
> Menu.StatusUnisysComp.BackColor = RGB(0, 255, 0)
> Else
> Menu.StatusUnisysComp.ForeColor = RGB(255, 0, 0)
> Menu.StatusUnisysComp.BackColor = RGB(255, 0, 0)
> End If
>
> If Menu.StatusNSR.Value = "Completed" Then
> Menu.StatusNSR.ForeColor = 0
> Menu.StatusNSR.BackColor = RGB(0, 255, 0)
> Else
> Menu.StatusNSR.ForeColor = RGB(255, 0, 0)
> Menu.StatusNSR.BackColor = RGB(255, 0, 0)
> End If
>
> --
> Trefor
>

[Trefor]

Jim,

Thankyou for the reply, but I think there is a little bit more that I need
here. I would like ANY mouse move on the form to search through a frame for
ANY text box and then update the color. Something like:

For each textbox in frame1
..
....
...
etc.

--
Trefor


"Jim Cone" wrote:

> Trefor,
> '----------------
> Private Sub Frame1_Click()
> Call FixThemColors(Me.Frame1.ActiveControl)
> End Sub
>
> Sub FixThemColors(ByRef objBox As MSForms.Control)
> If TypeName(objBox) = "TextBox" Then
> If objBox.Value = "Completed" Then
> objBox.ForeColor = 0
> objBox.BackColor = RGB(0, 255, 0)
> Else
> objBox.ForeColor = RGB(255, 0, 0)
> objBox.BackColor = RGB(255, 0, 0)
> End If
> End If
> End Sub
> '------------
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
>
>
> "Trefor" <[email protected]> wrote in message...
> On a userform I have a "Frame" containing a bunch of text boxes. If the text
> box contains the word "Completed" it changes the text box to Black on Green
> other it set the text bov to Red on Red. Below is the Module code I am using,
> as it stands I am going to have to code this for each and every text box in
> the frame. Is it possible to run a loop of some sort across all text boxes in
> the one frame?
>
>
> If Menu.StatusUnisysComp.Value = "Completed" Then
> Menu.StatusUnisysComp.ForeColor = 0
> Menu.StatusUnisysComp.BackColor = RGB(0, 255, 0)
> Else
> Menu.StatusUnisysComp.ForeColor = RGB(255, 0, 0)
> Menu.StatusUnisysComp.BackColor = RGB(255, 0, 0)
> End If
>
> If Menu.StatusNSR.Value = "Completed" Then
> Menu.StatusNSR.ForeColor = 0
> Menu.StatusNSR.BackColor = RGB(0, 255, 0)
> Else
> Menu.StatusNSR.ForeColor = RGB(255, 0, 0)
> Menu.StatusNSR.BackColor = RGB(255, 0, 0)
> End If
>
> --
> Trefor
>

[Trefor]

Glen,

Conditional Formatting would be perfect and I know how to do this on a
worksheet, how does this work on a userform?

--
Trefor


"Glen Mettler" wrote:

> Why not use Conditional Formating?
>
> Glen
>
> "Trefor" <[email protected]> wrote in message
> news:[email protected]...
> > On a userform I have a "Frame" containing a bunch of text boxes. If the
> > text
> > box contains the word "Completed" it changes the text box to Black on
> > Green
> > other it set the text bov to Red on Red. Below is the Module code I am
> > using,
> > as it stands I am going to have to code this for each and every text box
> > in
> > the frame. Is it possible to run a loop of some sort across all text boxes
> > in
> > the one frame?
> >
> >
> > If Menu.StatusUnisysComp.Value = "Completed" Then
> > Menu.StatusUnisysComp.ForeColor = 0
> > Menu.StatusUnisysComp.BackColor = RGB(0, 255, 0)
> > Else
> > Menu.StatusUnisysComp.ForeColor = RGB(255, 0, 0)
> > Menu.StatusUnisysComp.BackColor = RGB(255, 0, 0)
> > End If
> >
> > If Menu.StatusNSR.Value = "Completed" Then
> > Menu.StatusNSR.ForeColor = 0
> > Menu.StatusNSR.BackColor = RGB(0, 255, 0)
> > Else
> > Menu.StatusNSR.ForeColor = RGB(255, 0, 0)
> > Menu.StatusNSR.BackColor = RGB(255, 0, 0)
> > End If
> >
> > --
> > Trefor
> >
>
>
>
>

[Trefor]

Jim,

Thanks with your clues I have just managed to work the last bits out:

Call FixColors(Me.Project_Status.ActiveControl)


Sub FixColors(ByRef objBox As MSForms.Control)
For Each objBox In Menu.Controls
If TypeName(objBox) = "TextBox" And InStr(objBox.Name, "Status") > 0
Then
If objBox.Value = "Completed" Then
objBox.ForeColor = vbBlack
objBox.BackColor = vbGreen
ElseIf objBox.Value = "In Progress" Then
objBox.ForeColor = vbBlack
objBox.BackColor = RGB(255, 173, 66)
ElseIf objBox.Value = "" Then
objBox.ForeColor = vbRed
objBox.BackColor = vbRed
End If
End If
Next
End Sub

--
Trefor


"Trefor" wrote:

> Jim,
>
> Thankyou for the reply, but I think there is a little bit more that I need
> here. I would like ANY mouse move on the form to search through a frame for
> ANY text box and then update the color. Something like:
>
> For each textbox in frame1
> ..
> ...
> ..
> etc.
>
> --
> Trefor
>
>
> "Jim Cone" wrote:
>
> > Trefor,
> > '----------------
> > Private Sub Frame1_Click()
> > Call FixThemColors(Me.Frame1.ActiveControl)
> > End Sub
> >
> > Sub FixThemColors(ByRef objBox As MSForms.Control)
> > If TypeName(objBox) = "TextBox" Then
> > If objBox.Value = "Completed" Then
> > objBox.ForeColor = 0
> > objBox.BackColor = RGB(0, 255, 0)
> > Else
> > objBox.ForeColor = RGB(255, 0, 0)
> > objBox.BackColor = RGB(255, 0, 0)
> > End If
> > End If
> > End Sub
> > '------------
> > Jim Cone
> > San Francisco, USA
> > http://www.realezsites.com/bus/primitivesoftware
> >
> >
> > "Trefor" <[email protected]> wrote in message...
> > On a userform I have a "Frame" containing a bunch of text boxes. If the text
> > box contains the word "Completed" it changes the text box to Black on Green
> > other it set the text bov to Red on Red. Below is the Module code I am using,
> > as it stands I am going to have to code this for each and every text box in
> > the frame. Is it possible to run a loop of some sort across all text boxes in
> > the one frame?
> >
> >
> > If Menu.StatusUnisysComp.Value = "Completed" Then
> > Menu.StatusUnisysComp.ForeColor = 0
> > Menu.StatusUnisysComp.BackColor = RGB(0, 255, 0)
> > Else
> > Menu.StatusUnisysComp.ForeColor = RGB(255, 0, 0)
> > Menu.StatusUnisysComp.BackColor = RGB(255, 0, 0)
> > End If
> >
> > If Menu.StatusNSR.Value = "Completed" Then
> > Menu.StatusNSR.ForeColor = 0
> > Menu.StatusNSR.BackColor = RGB(0, 255, 0)
> > Else
> > Menu.StatusNSR.ForeColor = RGB(255, 0, 0)
> > Menu.StatusNSR.BackColor = RGB(255, 0, 0)
> > End If
> >
> > --
> > Trefor
> >

[Glen Mettler]

You can't do conditional formatting in forms. But you can do something like
this:

Private Sub TextBox1_Change()
TextBox2.Value = UserForm1.TextBox1.Value
If TextBox2.Value = 1 Then
TextBox2.ForeColor = &H80000002
TextBox2.BackColor = &H8000000F
End If
End Sub

Glen

"Trefor" <[email protected]> wrote in message
news:[email protected]...
> Glen,
>
> Conditional Formatting would be perfect and I know how to do this on a
> worksheet, how does this work on a userform?
>
> --
> Trefor
>
>
> "Glen Mettler" wrote:
>
>> Why not use Conditional Formating?
>>
>> Glen
>>
>> "Trefor" <[email protected]> wrote in message
>> news:[email protected]...
>> > On a userform I have a "Frame" containing a bunch of text boxes. If the
>> > text
>> > box contains the word "Completed" it changes the text box to Black on
>> > Green
>> > other it set the text bov to Red on Red. Below is the Module code I am
>> > using,
>> > as it stands I am going to have to code this for each and every text
>> > box
>> > in
>> > the frame. Is it possible to run a loop of some sort across all text
>> > boxes
>> > in
>> > the one frame?
>> >
>> >
>> > If Menu.StatusUnisysComp.Value = "Completed" Then
>> > Menu.StatusUnisysComp.ForeColor = 0
>> > Menu.StatusUnisysComp.BackColor = RGB(0, 255, 0)
>> > Else
>> > Menu.StatusUnisysComp.ForeColor = RGB(255, 0, 0)
>> > Menu.StatusUnisysComp.BackColor = RGB(255, 0, 0)
>> > End If
>> >
>> > If Menu.StatusNSR.Value = "Completed" Then
>> > Menu.StatusNSR.ForeColor = 0
>> > Menu.StatusNSR.BackColor = RGB(0, 255, 0)
>> > Else
>> > Menu.StatusNSR.ForeColor = RGB(255, 0, 0)
>> > Menu.StatusNSR.BackColor = RGB(255, 0, 0)
>> > End If
>> >
>> > --
>> > Trefor
>> >
>>
>>
>>
>>
>