CODEWITHSUNDEEP
vbaexcel
SKP
SKP

Reputation: 125

excel vba change textbox based on values

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

Upvotes: 0

Views: 19639

Answers (2)

Dubison
Dubison

Reputation: 768

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

enter image description here

enter image description here

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

enter image description here

enter image description here

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

Upvotes: 1

Alan K
Alan K

Reputation: 2017

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

Upvotes: 2

Related Questions