Text box format VBA

[gregork]

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

 

[John Wilson]

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]

Perfect, many thanks John.

Kind Regards
gregork

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

 

[John Wilson]

gregork,

Happy to have been a help.
Thanks for the reply.

John

 

[gregork]

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

 

[Harald Staff]

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"

 

[gregork]

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

 

[Tom Ogilvy]

That would be good.

--
Regards,
Tom Ogilvy

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

 

[gregork]

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]

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.

 

[John Wilson]

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

 

[Harald Staff]

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

 

[gregork]

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

 

[Harald Staff]

If TextBox27.Text = "Fail" Then
TextBox27.BackColor = vbRed
Else
TextBox27.BackColor = vbGreen
End If

Note the line breaks.

 

[gregork]

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