TextBox Active X >= Cell

[CWMExcel]

Hi All,

I am new to the site, and I have come unstuck on something that should be simple.

I am trying to create a excel front page that shows a figure in an Activex text box that is changed in colour from Red to Green depending on the value between two cells. I have got the code to work fine with the below example if I type in the >= figure as part of the code, but I cannot get the VB code to see the cell "X5" which would enable me to change the figure within the sheet.

As it stands the ActiveX textbox obtains one of the values via the properties LinkedCell option "T5" , and works as below. (This correctly changes the colour of the text from Green to Red if the value in the Activex Text box is less than 60.

--------------------
Private Sub TextBox3_Change()
Dim x
x = IIf(TextBox4.Value >= 60, RGB(255, 0, 0), RGB(0, 176, 80))
Me.TextBox3.ForeColor = x
End Sub

---------------------

So I try the below to enable me to use the >= value from the Cell "X5" so that the figure can be modified for different values and disciplines without changing the code. The VB is ok but it just doesn't work, I have also tried using .text instead of .value but then I get mixed results and random figures below 100 change from red to green and anything over 100 is just Green. I have tried Cell.Range, Cell.Value, Ranges Etc

Private Sub TextBox3_Change()
Dim x
x = IIf(TextBox3.Value >= ActiveSheet.Range("X5").Value, RGB(255, 0, 0), RGB(0, 176, 80))
Me.TextBox3.ForeColor = x
End Sub

Pulling my hair out on this and I am probably missing something obvious. So any help gratefully appreciated.

Thanks
Christopher.

 

[CalcSux78]

Welcome to the forum, Christopher!

You might read up more on IIF vs IF Statements: IIF vs. If...Then...Else in VB, VBA, and VB .NET

Try:

Private Sub TextBox3_Change()
Dim x
If TextBox3.Value >= ActiveSheet.Range("X5").Value Then
    x = RGB(255, 0, 0)
Else
    x = RGB(0, 176, 80)
End If
Me.TextBox3.ForeColor = x
End Sub

 

[John_w]

Welcome to MrExcel forums.

This is a subtle bug and not very obvious. TextBox values are strings and to compare it to a number in a cell you must convert the textbox value to a numeric value using CDbl (if the textbox can contain decimal values), CLng (whole number values) or CInt, like this:

Private Sub TextBox3_Change()

    Dim x As Long
    
    If TextBox3.Value <> "" Then
        x = IIf(CDbl(TextBox3.Value) >= ActiveSheet.Range("X5").Value, RGB(255, 0, 0), RGB(0, 176, 80))
        TextBox3.ForeColor = x
    End If

End Sub

PS - I don't think there is anything wrong with using IIf in this case.

 

[CWMExcel]

Hi, John_W and CalcSux78,

Thanks for your reply, it has worked. Its great to be able to pool on other minds. I think IIF is ok also and appears to work ok otherwise. I though it was something to do with the text vs value but just hit a wall.

Thanks so much.

 

[CalcSux78]

Good catch, John! Cunningham's Law worked out this time