| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text box format VBA
Hi All,
Can you conditionally format a text box back colour on a user form? For instance if the value in textbox1 was not in between (sheet1 cell= C1 and sheet1 cell= C2) make the textbox1 back colour red. How would you do this? Regards gregork |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Text box format VBA
gregork,
In the TextBox AfterUpdate Event. Private Sub TextBox1_AfterUpdate() If Val(TextBox1.Value) Worksheets("Sheet1").Range("C1").Value And _ Val(TextBox1.Value) < Worksheets("Sheet1").Range("C2").Value Then TextBox1.BackColor = vbRed Else TextBox1.BackColor = vbWhite End If End Sub John "gregork" wrote in message ... Hi All, Can you conditionally format a text box back colour on a user form? For instance if the value in textbox1 was not in between (sheet1 cell= C1 and sheet1 cell= C2) make the textbox1 back colour red. How would you do this? Regards gregork |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Text box format VBA
Perfect, many thanks John.
Kind Regards gregork "John Wilson" wrote in message ... gregork, In the TextBox AfterUpdate Event. Private Sub TextBox1_AfterUpdate() If Val(TextBox1.Value) Worksheets("Sheet1").Range("C1").Value And _ Val(TextBox1.Value) < Worksheets("Sheet1").Range("C2").Value Then TextBox1.BackColor = vbRed Else TextBox1.BackColor = vbWhite End If End Sub John "gregork" wrote in message ... Hi All, Can you conditionally format a text box back colour on a user form? For instance if the value in textbox1 was not in between (sheet1 cell= C1 and sheet1 cell= C2) make the textbox1 back colour red. How would you do this? Regards gregork |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Text box format VBA
gregork,
Happy to have been a help. Thanks for the reply. John "gregork" wrote in message ... Perfect, many thanks John. Kind Regards gregork "John Wilson" wrote in message ... gregork, In the TextBox AfterUpdate Event. Private Sub TextBox1_AfterUpdate() If Val(TextBox1.Value) Worksheets("Sheet1").Range("C1").Value And _ Val(TextBox1.Value) < Worksheets("Sheet1").Range("C2").Value Then TextBox1.BackColor = vbRed Else TextBox1.BackColor = vbWhite End If End Sub John "gregork" wrote in message ... Hi All, Can you conditionally format a text box back colour on a user form? For instance if the value in textbox1 was not in between (sheet1 cell= C1 and sheet1 cell= C2) make the textbox1 back colour red. How would you do this? Regards gregork |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Text box format VBA
John I hope your still out there. I'm having trouble with another formatting
problem. I want to format back colour based on text in a textbox. i.e. If textbox27 text="fail" then TextBox27.BackColor = &HC0C0FF. I've tried variations on the code you gave me but I can't quite make it happen. Kind Regards gregork "John Wilson" wrote in message ... gregork, Happy to have been a help. Thanks for the reply. John "gregork" wrote in message ... Perfect, many thanks John. Kind Regards gregork "John Wilson" wrote in message ... gregork, In the TextBox AfterUpdate Event. Private Sub TextBox1_AfterUpdate() If Val(TextBox1.Value) Worksheets("Sheet1").Range("C1").Value And _ Val(TextBox1.Value) < Worksheets("Sheet1").Range("C2").Value Then TextBox1.BackColor = vbRed Else TextBox1.BackColor = vbWhite End If End Sub John "gregork" wrote in message ... Hi All, Can you conditionally format a text box back colour on a user form? For instance if the value in textbox1 was not in between (sheet1 cell= C1 and sheet1 cell= C2) make the textbox1 back colour red. How would you do this? Regards gregork |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Text box format VBA
"gregork" wrote in message
... I want to format back colour based on text in a textbox. i.e. If textbox27 text="fail" then TextBox27.BackColor = &HC0C0FF. You are very close: If TextBox27.Text = "fail" Then TextBox27.BackColor = "&HC0C0FF" Else TextBox27.BackColor = "&HFFFFFF" End If But consider uppercase, mixed case, part entries, trailing spaces... If InStr(Trim$(LCase$(TextBox27.Text)), "fail") 0 Then reacts also on entry " You FAILED madam" -- HTH. Best wishes Harald Followup to newsgroup only please. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Text box format VBA
Thanks for the reply Harold.Sorry to say I can't get the code to work.
Should I have pasted it into an after update event? Any suggestions appreciated. Many thanks gregork "Harald Staff" wrote in message ... "gregork" wrote in message ... I want to format back colour based on text in a textbox. i.e. If textbox27 text="fail" then TextBox27.BackColor = &HC0C0FF. You are very close: If TextBox27.Text = "fail" Then TextBox27.BackColor = "&HC0C0FF" Else TextBox27.BackColor = "&HFFFFFF" End If But consider uppercase, mixed case, part entries, trailing spaces... If InStr(Trim$(LCase$(TextBox27.Text)), "fail") 0 Then reacts also on entry " You FAILED madam" -- HTH. Best wishes Harald Followup to newsgroup only please. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Text box format VBA
That would be good.
-- Regards, Tom Ogilvy gregork wrote in message ... Thanks for the reply Harold.Sorry to say I can't get the code to work. Should I have pasted it into an after update event? Any suggestions appreciated. Many thanks gregork "Harald Staff" wrote in message ... "gregork" wrote in message ... I want to format back colour based on text in a textbox. i.e. If textbox27 text="fail" then TextBox27.BackColor = &HC0C0FF. You are very close: If TextBox27.Text = "fail" Then TextBox27.BackColor = "&HC0C0FF" Else TextBox27.BackColor = "&HFFFFFF" End If But consider uppercase, mixed case, part entries, trailing spaces... If InStr(Trim$(LCase$(TextBox27.Text)), "fail") 0 Then reacts also on entry " You FAILED madam" -- HTH. Best wishes Harald Followup to newsgroup only please. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Text box format VBA
Thanks for your input Tom. Sorry I didn't explain myself properly. What I
meant by " Should I have pasted it into an after update event?" was I have tried it in the Private Sub TextBox27_AfterUpdate() event and it is not working should I have put it in a different event? Regards gregork "Tom Ogilvy" wrote in message ... That would be good. -- Regards, Tom Ogilvy gregork wrote in message ... Thanks for the reply Harold.Sorry to say I can't get the code to work. Should I have pasted it into an after update event? Any suggestions appreciated. Many thanks gregork "Harald Staff" wrote in message ... "gregork" wrote in message ... I want to format back colour based on text in a textbox. i.e. If textbox27 text="fail" then TextBox27.BackColor = &HC0C0FF. You are very close: If TextBox27.Text = "fail" Then TextBox27.BackColor = "&HC0C0FF" Else TextBox27.BackColor = "&HFFFFFF" End If But consider uppercase, mixed case, part entries, trailing spaces... If InStr(Trim$(LCase$(TextBox27.Text)), "fail") 0 Then reacts also on entry " You FAILED madam" -- HTH. Best wishes Harald Followup to newsgroup only please. |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Text box format VBA
Private Sub TextBox27_AfterUpdate()
If LCase(Trim(TextBox27.Text)) = "fail" Then TextBox27.BackColor = "&HC0C0FF" Else TextBox27.BackColor = "&HFFFFFF" End If End Sub worked fine for me - although you have to have more than one control on the form or there is no afterupdate. -- Regards, Tom Ogilvy gregork wrote in message ... Thanks for your input Tom. Sorry I didn't explain myself properly. What I meant by " Should I have pasted it into an after update event?" was I have tried it in the Private Sub TextBox27_AfterUpdate() event and it is not working should I have put it in a different event? Regards gregork "Tom Ogilvy" wrote in message ... That would be good. -- Regards, Tom Ogilvy gregork wrote in message ... Thanks for the reply Harold.Sorry to say I can't get the code to work. Should I have pasted it into an after update event? Any suggestions appreciated. Many thanks gregork "Harald Staff" wrote in message ... "gregork" wrote in message ... I want to format back colour based on text in a textbox. i.e. If textbox27 text="fail" then TextBox27.BackColor = &HC0C0FF. You are very close: If TextBox27.Text = "fail" Then TextBox27.BackColor = "&HC0C0FF" Else TextBox27.BackColor = "&HFFFFFF" End If But consider uppercase, mixed case, part entries, trailing spaces... If InStr(Trim$(LCase$(TextBox27.Text)), "fail") 0 Then reacts also on entry " You FAILED madam" -- HTH. Best wishes Harald Followup to newsgroup only please. |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Text box format VBA
gregork,
Yes, it should have been pasted into the AfterUpdate event. Looking at the code that you were given, it should work too. Personally, I tend to shy away from that "&HC0C0FF" stuff whenever I can. "vbRed", "vbGreen", etc. is a lot easier to understand. The ColorIndex 3, ColorIndex 4, etc. can usually get me what I need and is still easier to understand. For help on colors, take a look at this site: http://www.mvps.org/dmcritchie/excel/colors.htm If you still can't get this to work, post your code (and where you have it) and someone will surely do their best to help you. John "gregork" wrote in message ... Thanks for your input Tom. Sorry I didn't explain myself properly. What I meant by " Should I have pasted it into an after update event?" was I have tried it in the Private Sub TextBox27_AfterUpdate() event and it is not working should I have put it in a different event? Regards gregork "Tom Ogilvy" wrote in message ... That would be good. -- Regards, Tom Ogilvy gregork wrote in message ... Thanks for the reply Harold.Sorry to say I can't get the code to work. Should I have pasted it into an after update event? Any suggestions appreciated. Many thanks gregork "Harald Staff" wrote in message ... "gregork" wrote in message ... I want to format back colour based on text in a textbox. i.e. If textbox27 text="fail" then TextBox27.BackColor = &HC0C0FF. You are very close: If TextBox27.Text = "fail" Then TextBox27.BackColor = "&HC0C0FF" Else TextBox27.BackColor = "&HFFFFFF" End If But consider uppercase, mixed case, part entries, trailing spaces... If InStr(Trim$(LCase$(TextBox27.Text)), "fail") 0 Then reacts also on entry " You FAILED madam" -- HTH. Best wishes Harald Followup to newsgroup only please. |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Text box format VBA
"John Wilson" skrev i melding
... Personally, I tend to shy away from that "&HC0C0FF" stuff whenever I can. Seemed like what the OP wanted. And for color it's very familiar for the ones among us that writes HTML "by hand" in pad-like text editors. But I guess we're a minority :-) -- HTH. Best wishes Harald Followup to newsgroup only please |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Text box format VBA
Sorry gentlemen I still can't get the thing to work. Here's the code. The
private sub textbox1 after update code works perfectly. I must be missing something obvious here??? Private Sub UserForm_Initialize() Me.TextBox1.Value = Sheets("Blend Sheet").Range("ac7").Text Me.TextBox27.Value = Sheets("Blend Sheet").Range("ac16").Text End Sub Private Sub CommandButton1_Click() Unload Me End Sub Private Sub TextBox1_AfterUpdate() If Val(TextBox1.Value) Worksheets("Blend Sheet").Range("V7").Value And _ Val(TextBox1.Value) < Worksheets("Blend Sheet").Range("V6").Value Then TextBox1.BackColor = &HC0C0FF Else TextBox1.BackColor = vbWhite End If End Sub Private Sub TextBox27_AfterUpdate() If TextBox27.Text = "Fail" Then TextBox27.BackColor = vbRed Else TextBox27.BackColor = vbGreen End If End Sub Many Thanks gregork "John Wilson" wrote in message ... gregork, Yes, it should have been pasted into the AfterUpdate event. Looking at the code that you were given, it should work too. Personally, I tend to shy away from that "&HC0C0FF" stuff whenever I can. "vbRed", "vbGreen", etc. is a lot easier to understand. The ColorIndex 3, ColorIndex 4, etc. can usually get me what I need and is still easier to understand. For help on colors, take a look at this site: http://www.mvps.org/dmcritchie/excel/colors.htm If you still can't get this to work, post your code (and where you have it) and someone will surely do their best to help you. John "gregork" wrote in message ... Thanks for your input Tom. Sorry I didn't explain myself properly. What I meant by " Should I have pasted it into an after update event?" was I have tried it in the Private Sub TextBox27_AfterUpdate() event and it is not working should I have put it in a different event? Regards gregork "Tom Ogilvy" wrote in message ... That would be good. -- Regards, Tom Ogilvy gregork wrote in message ... Thanks for the reply Harold.Sorry to say I can't get the code to work. Should I have pasted it into an after update event? Any suggestions appreciated. Many thanks gregork "Harald Staff" wrote in message ... "gregork" wrote in message ... I want to format back colour based on text in a textbox. i.e. If textbox27 text="fail" then TextBox27.BackColor = &HC0C0FF. You are very close: If TextBox27.Text = "fail" Then TextBox27.BackColor = "&HC0C0FF" Else TextBox27.BackColor = "&HFFFFFF" End If But consider uppercase, mixed case, part entries, trailing spaces... If InStr(Trim$(LCase$(TextBox27.Text)), "fail") 0 Then reacts also on entry " You FAILED madam" -- HTH. Best wishes Harald Followup to newsgroup only please. |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Text box format VBA
If TextBox27.Text = "Fail" Then
TextBox27.BackColor = vbRed Else TextBox27.BackColor = vbGreen End If Note the line breaks. -- HTH. Best wishes Harald Followup to newsgroup only please "gregork" skrev i melding ... Sorry gentlemen I still can't get the thing to work. Here's the code. The private sub textbox1 after update code works perfectly. I must be missing something obvious here??? Private Sub UserForm_Initialize() Me.TextBox1.Value = Sheets("Blend Sheet").Range("ac7").Text Me.TextBox27.Value = Sheets("Blend Sheet").Range("ac16").Text End Sub Private Sub CommandButton1_Click() Unload Me End Sub Private Sub TextBox1_AfterUpdate() If Val(TextBox1.Value) Worksheets("Blend Sheet").Range("V7").Value And _ Val(TextBox1.Value) < Worksheets("Blend Sheet").Range("V6").Value Then TextBox1.BackColor = &HC0C0FF Else TextBox1.BackColor = vbWhite End If End Sub Private Sub TextBox27_AfterUpdate() If TextBox27.Text = "Fail" Then TextBox27.BackColor = vbRed Else TextBox27.BackColor = vbGreen End If End Sub Many Thanks gregork "John Wilson" wrote in message ... gregork, Yes, it should have been pasted into the AfterUpdate event. Looking at the code that you were given, it should work too. Personally, I tend to shy away from that "&HC0C0FF" stuff whenever I can. "vbRed", "vbGreen", etc. is a lot easier to understand. The ColorIndex 3, ColorIndex 4, etc. can usually get me what I need and is still easier to understand. For help on colors, take a look at this site: http://www.mvps.org/dmcritchie/excel/colors.htm If you still can't get this to work, post your code (and where you have it) and someone will surely do their best to help you. John "gregork" wrote in message ... Thanks for your input Tom. Sorry I didn't explain myself properly. What I meant by " Should I have pasted it into an after update event?" was I have tried it in the Private Sub TextBox27_AfterUpdate() event and it is not working should I have put it in a different event? Regards gregork "Tom Ogilvy" wrote in message ... That would be good. -- Regards, Tom Ogilvy gregork wrote in message ... Thanks for the reply Harold.Sorry to say I can't get the code to work. Should I have pasted it into an after update event? Any suggestions appreciated. Many thanks gregork "Harald Staff" wrote in message ... "gregork" wrote in message ... I want to format back colour based on text in a textbox. i.e. If textbox27 text="fail" then TextBox27.BackColor = &HC0C0FF. You are very close: If TextBox27.Text = "fail" Then TextBox27.BackColor = "&HC0C0FF" Else TextBox27.BackColor = "&HFFFFFF" End If But consider uppercase, mixed case, part entries, trailing spaces... If InStr(Trim$(LCase$(TextBox27.Text)), "fail") 0 Then reacts also on entry " You FAILED madam" -- HTH. Best wishes Harald Followup to newsgroup only please. |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Text box format VBA
Hello Harald,
It still did not work. I transposed the code exactly how you sent it. Then I messed around with the event and tried the code with this:" Private Sub TextBox27_Change()" and blow me down it works. Thank you all for your kind help. Regards gregork "Harald Staff" wrote in message ... If TextBox27.Text = "Fail" Then TextBox27.BackColor = vbRed Else TextBox27.BackColor = vbGreen End If Note the line breaks. -- HTH. Best wishes Harald Followup to newsgroup only please "gregork" skrev i melding ... Sorry gentlemen I still can't get the thing to work. Here's the code. The private sub textbox1 after update code works perfectly. I must be missing something obvious here??? Private Sub UserForm_Initialize() Me.TextBox1.Value = Sheets("Blend Sheet").Range("ac7").Text Me.TextBox27.Value = Sheets("Blend Sheet").Range("ac16").Text End Sub Private Sub CommandButton1_Click() Unload Me End Sub Private Sub TextBox1_AfterUpdate() If Val(TextBox1.Value) Worksheets("Blend Sheet").Range("V7").Value And _ Val(TextBox1.Value) < Worksheets("Blend Sheet").Range("V6").Value Then TextBox1.BackColor = &HC0C0FF Else TextBox1.BackColor = vbWhite End If End Sub Private Sub TextBox27_AfterUpdate() If TextBox27.Text = "Fail" Then TextBox27.BackColor = vbRed Else TextBox27.BackColor = vbGreen End If End Sub Many Thanks gregork "John Wilson" wrote in message ... gregork, Yes, it should have been pasted into the AfterUpdate event. Looking at the code that you were given, it should work too. Personally, I tend to shy away from that "&HC0C0FF" stuff whenever I can. "vbRed", "vbGreen", etc. is a lot easier to understand. The ColorIndex 3, ColorIndex 4, etc. can usually get me what I need and is still easier to understand. For help on colors, take a look at this site: http://www.mvps.org/dmcritchie/excel/colors.htm If you still can't get this to work, post your code (and where you have it) and someone will surely do their best to help you. John "gregork" wrote in message ... Thanks for your input Tom. Sorry I didn't explain myself properly. What I meant by " Should I have pasted it into an after update event?" was I have tried it in the Private Sub TextBox27_AfterUpdate() event and it is not working should I have put it in a different event? Regards gregork "Tom Ogilvy" wrote in message ... That would be good. -- Regards, Tom Ogilvy gregork wrote in message ... Thanks for the reply Harold.Sorry to say I can't get the code to work. Should I have pasted it into an after update event? Any suggestions appreciated. Many thanks gregork "Harald Staff" wrote in message ... "gregork" wrote in message ... I want to format back colour based on text in a textbox. i.e. If textbox27 text="fail" then TextBox27.BackColor = &HC0C0FF. You are very close: If TextBox27.Text = "fail" Then TextBox27.BackColor = "&HC0C0FF" Else TextBox27.BackColor = "&HFFFFFF" End If But consider uppercase, mixed case, part entries, trailing spaces... If InStr(Trim$(LCase$(TextBox27.Text)), "fail") 0 Then reacts also on entry " You FAILED madam" -- HTH. Best wishes Harald Followup to newsgroup only please. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| from date format convert to text format is wrong | Excel Discussion (Misc queries) | |||
| Need help with converting CUSTOM format/TEXT format to DATE format | Excel Worksheet Functions | |||
| Change Date Format to Specific Text Format When Copying | Excel Discussion (Misc queries) | |||
| Change number (in text format) to numeric format | Excel Discussion (Misc queries) | |||
| how to format excel format to text format with separator "|" in s. | New Users to Excel | |||
Linear Mode
