Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Textbox Backcolor and Forecolor at Runtime
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Textbox Backcolor and Forecolor at Runtime
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Textbox Backcolor and Forecolor at Runtime
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Textbox Backcolor and Forecolor at Runtime
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Textbox Backcolor and Forecolor at Runtime
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" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Textbox Backcolor and Forecolor at Runtime
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" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Textbox Backcolor and Forecolor at Runtime
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" wrote in message ... 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Textbox Backcolor and Forecolor at Runtime
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change forecolor with code | Excel Discussion (Misc queries) | |||
Fill.backcolor and forecolor do not consistent in Chart and Shapes | Excel Programming | |||
TextBox BackColor | Excel Programming | |||
BackColor, Forecolor, Bold, and Font Size for ComboBox in Commandbar | Excel Programming | |||
BackColor, Forecolor, Bold, and Font Size for ComboBox in Commandbar | Excel Programming |