excel vba change textbox based on values

Question

I am trying to change the font color based on the values in the cells(16,3). For some reason it is not working. The present value at 16,3 is 94% and it is showing up in green color instead of amber. Your help would be appreciated.

Private Sub TextBox1_Change()

    TextBox1.Value = Cells(16, 3).Text
    Cells(16, 3).Font.Bold = True

    If TextBox1.Value > 95 Then TextBox1.ForeColor = RGB(0, 128, 0)  'Green

    If TextBox1.Value > 80 And TextBox1.Value < 95 Then TextBox1.ForeColor = RGB(255, 153, 0)  'Amber

    If TextBox1.Value < 80 Then TextBox1.ForeColor = RGB(255, 0, 0)  'Red

End Sub

Try adding a breakpoint to your code at the line If TextBox1.Value > 95 [...] and then mouse over the TextBox.Value part to check the value of it from the code's point of view. I can't really see an error in your code so we're going to need to check that the textbox is picking up the correct value. Also is this textbox on a userform or on the worksheet itself? — Aiken
– Aiken, Commented Feb 6, 2015 at 8:34
I checked that now. The value in the cell is in percentage 94%. I have used the following to arrive at it. TextBox1.Value = Format(Cells(16, 3), "00%") — SKP
– SKP, Commented Feb 6, 2015 at 8:42
use .value insead of .text while defining textbox1 value. By that you will see the real value which is calculated in your if statements. — Dubison
– Dubison, Commented Feb 6, 2015 at 9:28

Alan K · Accepted Answer · 2015-02-06 10:00:56Z

2

You'll get that result if the value in the cell is in text format rather than as an actual value. Is it possible that that's the case?

The other thing to bear in mind, though this wouldn't be the cause of your existing problem or the text would always be red, is that 94% is actually 0.94, not 94.

Edit: (I'm doing an edit to my original answer in response to the comment because I need to include code which does not go into comments well)

You have a few other problems.

The first is that if this is being driven by the value in cell(16,3) I'm not sure that you should be driving it from the Textbox1_Change event. By doing that you are waiting for someone to enter a value into that text box and immediately overwriting it with whatever is in that cell. Better to populate the entry during the form load.

Much depends on what you're trying to do with this, and I have no information on that.

Second, if you step through the code you'll find that TextBox1.Value is returning a string; it has double quotes around it. But you are comparing it to a numeric set of values.

The third is that your code does not deal with the situation where there is an exact match of (say) 80%.

The fourth is that if you suck the value straight from the cell, and it really is a percentage value, it'll come up as 0.94, not formatted as a percentage.

This deals with the lot.

Dim rng As Excel.Range

Set rng = ActiveSheet.Cells(16,3)

TextBox1.Value = Format(rng.Value, "00%")
rng.Font.Bold = True

'You should really implement error checking here.
'There's no point reading the value from the textbox if it's coming from a cell.
'Just use the cell value.
If rng.Value >= 0.95 Then
    TextBox1.ForeColor = RGB(0, 128, 0) 'Green
ElseIf rng.Value >= 0.8 And rng.Value < 0.95 Then
    TextBox1.ForeColor = RGB(255, 153, 0) 'Amber
ElseIf rng.Value < 0.8 Then
    TextBox1.ForeColor = RGB(255, 0, 0) 'Red 
End If

Set rng = Nothing

edited Feb 6, 2015 at 10:00

answered Feb 6, 2015 at 8:43

Alan K

2,0173 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SKP Over a year ago

I have tried even the 0.94 and it did not work. Private Sub TextBox1_Change() TextBox1.Value = Format(Cells(16, 3), "00%") Cells(16, 3).Font.Bold = True If TextBox1.Value > 0.95 Then TextBox1.ForeColor = RGB(0, 128, 0) 'Green If TextBox1.Value > 0.8 And TextBox1.Value < 0.94 Then TextBox1.ForeColor = RGB(255, 153, 0) 'Amber If TextBox1.Value < 0.8 Then TextBox1.ForeColor = RGB(255, 0, 0) 'Red End Sub

SKP Over a year ago

Thanks a ton ALAN K. Your code worked. Learnt useful stuff from you.

Dubison · Accepted Answer · 2015-02-06 10:14:41Z

If you use

TextBox1.Value = Cells(16, 3).Value

instead of

TextBox1.Value = Cells(16, 3).Text

You will see the real values used in your if statements. And result will be like below:

If Text Type of related cell is : "General"

enter image description here

And if you use related cell type as "Percentage". You will get following results

enter image description here

So, depending on your preferences you can either use the cell value as general or you can convert it in the macro from percentage to general and display it as it is.

If you want to keep using Percentage in your related sheet which means actually as value you will have 0.XX in that cell however it will display as XX%, then you can simply multiply your value with 100 as follows:

TextBox1.Value = Cells(16, 3).Value * 100

Collectives™ on Stack Overflow

excel vba change textbox based on values

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related