| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Colour changes on User Form
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Multiple Colour changes on User Form
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" 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Multiple Colour changes on User Form
Why not use Conditional Formating?
Glen "Trefor" 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Multiple Colour changes on User Form
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" 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Multiple Colour changes on User Form
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" 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Multiple Colour changes on User Form
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" 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Multiple Colour changes on User Form
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" wrote in message ... 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" 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Multiple sheets, one user form, and a unique ID | Excel Programming | |||
| User Form to Filter by Multiple Criteria | Excel Programming | |||
| How to: User Form to assign a user defined range to a macro variab | Excel Programming | |||
| USer Form Entry to multiple cells | Excel Programming | |||
Linear Mode
