VBA Changes TextBox.BackColor, but keeps getting different results.

[shamsul]

I tried to make a textbox in a userform changes its background, green if less than 100% and red if more or equal to 100%. So far I made 2 different codes and I got 2 different results!

The first code is:

Private Sub CommandButton1_Click()

TextBox1.Value = Sheets("Sheet1").Range("I9").Value
TextBox1.Value = Format(TextBox1.Value, "0.00")

If TextBox1.Value >= "1" Then
TextBox1.BackColor = vbRed
Else: TextBox1.BackColor = vbGreen
End If

End Sub

And I got the correct result, but the format is not in percentage.

The second code is:

Private Sub CommandButton2_Click()

TextBox2.Value = Sheets("Sheet1").Range("I9").Value
TextBox2.Value = Format(TextBox2.Value, "0.00%")

If TextBox2.Value >= "1" Then
TextBox2.BackColor = vbRed
Else: TextBox2.BackColor = vbGreen
End If

End Sub

I managed to change the format to percentage, but I got the background color wrong.

What is wrong with my code in the second one? I did the same thing, except that I changed the format from 0.00 to 0.00%, and somehow I got the background the other way around.

Is there any way for me to rectify this? I want to display it as percentage, and get the background color to be green if its less than 100%, and red if equal or more than 100%.

Thank you in advance.

[xladept]

Take the quotes off of the "1" it makes it a string

If TextBox1.Value >= 1 Then

[shamsul]

Take the quotes off of the "1" it makes it a string

If TextBox1.Value >= 1 Then

Tried but it did not work

[xladept]

Try:

If TextBox1.Value >= .01 Then

[shamsul]

Try:

If TextBox1.Value >= .01 Then

Still cannot get it to work, but I found a pattern. My If/Then command only works if it formats are either "0", "0.0", or "0.00". Not sure if it helps though.

[xladept]

It turned red for me???

[shamsul]

It turned red for me???

You mean both commands turned red for you?

[jolivanes]

Is this of any help to you? (jindon's solution in Post #2)
http://www.ozgrid.com/forum/showthread.php?t=167624

[shamsul]

Is this of any help to you? (jindon's solution in Post #2)
http://www.ozgrid.com/forum/showthread.php?t=167624

I changed from

If Val(Replace(Me.TextBox2.Value, "%", "")) < 0

to

If Val(Replace(Me.TextBox2.Value, "%", "")) < 100

and it worked! But I dont understand what does the command do?

[Logit]

shamsul:

Here's what I get without changing any of your code:

Test.jpg

[xladept]

Glad it's working

[jolivanes]

Here is another one to play around with.
Based on Post #8 info from jindon

[mikerickson]

Try

If Val(TextBox1.Text) < 1 Then
    TextBox1.BackColor = vbGreen
Else
    TextBox1.BackColor = vbRed
End IF

[jolivanes]

But would it not be easier to format the textbox on the cell value?

[mikerickson]

I'm not sure what you mean by "format the text box on the cell value"
TextBox's don't have formats. They only contain strings, never numbers.

A "numeral" is a string that represents a number. Textboxes can contain numerals, like "1" or "1.00" or "001". But Textboxes will never "see" them as equal, even though they represent the same number.

When one wants to deal with the number that a TextBox.Text represents, one has to convert that string into a number. (The Val function in my code.)
The relationship "less than" is a particular problem, because the string "10" is less than the string "2" (just as "Al" comes before "I" in the dictionary) while the numbers that they represent have the opposite relationship.

It is easier to convert the numerals in text boxes to numbers than to fiddle with string based workarounds (e.g. making all numerals have the same number of characters like "10" vs "02").

Convert the TextBox entries to numbers, handle those numbers as numbers, and then convert back to a string when putting the result back into a text box is the easiest approach for me.

Excel VBA can be confusing around this issue because MicroSoft has programmed automatic type conversion into some of its features. But not all. It is better to explicitly convert between data types that to try to figure out (and remember and hope that the person who later uses your code also knows) if and when Excel will do data type conversions automatically.

[jolivanes]

Sorry about the poor wording Mike.
I meant the background color.
We are putting a cell value into the textbox so if that value in the cell >50 then color the textbox orange.

[mikerickson]

My post was responding to the OP that only mentioned Red and Green. (It looks like orange came from the linked question)

From the posts in this thread, i'm guessing that someone is putting a % in a text box. I have difficulty convincing folks that if a text box always shows a percent, the coding is easier when there is a fixed Label with the % sign next to a text box with a pure numeral in it (only characters are 0123456789.-)

[jolivanes]

I am not doubting your knowledge/wisdom by any means Mike.
After reading some posts where people had trouble showing what they wanted to see in the textbox I thought along the lines of referencing the cell value.
Just another way of tackling the problem.
The orange is just any kind of color.

[mikerickson]

Thats a good point of view.

I think of a user form as a mostly stand-alone object that does thing to a workbook. But the notion that it is a co-user interface in conduction with the workbook is a useful way to approach it.