VBA Code to Change Textbox Font Color on Value

[Justinian]

I have several textboxes on my dashboard. Each textbox is linked to a separate cell that shows a number, either negative or positive. I want the textbox number to change font color (green for positive number and red for negative number). I found the code below and it works but it only works when I run the macro with alt + F8. I want the macro to run on its own whenever the cell content changes so the textbox number font colro changes.

How can I later this code?

Sub Color_Text_InBox()
ActiveSheet.Shapes.Range(Array("TextBox 1")).Select

If Range("a1").Value < 0 Then
'<0 is better
With Selection.ShapeRange.TextFrame2.TextRange.Font.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(0, 176, 80)
.Transparency = 0
.Solid
End With
Selection.ShapeRange.TextFrame2.TextRange.Font.Bold = msoTrue
Else
'color the text box to show "not better"
With Selection.ShapeRange.TextFrame2.TextRange.Font.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0
.Solid
End With
Selection.ShapeRange.TextFrame2.TextRange.Font.Bold = msoTrue
End If

End Sub

 

[JoeMo]

If you're satisfied with the code you posted and the change to cell A1 is done manually you can use a worksheet_change module to call the code you posted. See below for installing the worksheet_change code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
    Call Color_Text_InBox
End If
End Sub

To install sheet code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.

 

[Justinian]

I figured out how to do it with the code below but it only works for "textbox 1."

How do I get this to work for all 12 textboxes?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

ActiveSheet.Shapes.Range(Array("TextBox 1")).Select

If Range("g99").Value < 0 Then
'<0 is better
With Selection.ShapeRange.TextFrame2.TextRange.Font.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0
.Solid
End With
Else
'color the text box to show "not better"
With Selection.ShapeRange.TextFrame2.TextRange.Font.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(0, 255, 0)
.Transparency = 0
.Solid
End With
End If

Range("A1").Select

End Sub

 

[JoeMo]

I figured out how to do it with the code below but it only works for "textbox 1."

How do I get this to work for all 12 textboxes?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

ActiveSheet.Shapes.Range(Array("TextBox 1")).Select

If Range("g99").Value < 0 Then
'<0 is better
With Selection.ShapeRange.TextFrame2.TextRange.Font.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0
.Solid
End With
Else
'color the text box to show "not better"
With Selection.ShapeRange.TextFrame2.TextRange.Font.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(0, 255, 0)
.Transparency = 0
.Solid
End With
End If

Range("A1").Select

End Sub

Your code is going to run at every change in the worksheet, not just on changes in cell A1. That's going to be a problem. If the other 11 textboxes are linked to other cells on the sheet that are changed manually you can modify the code I posted to determine which of the linked cells are changed and run a modified version of Color_Text_InBox code for each changed cell.

 

[Justinian]

The code you wrote is giving me a compile error (sub or function not defined) and it highlights:

Call Color_Text_InBox

 

[JoeMo]

The code you wrote is giving me a compile error (sub or function not defined) and it highlights:

Call Color_Text_InBox

Works fine for me. Do you have a sub named Color_Text_InBox in your standard modules (not part of sheet code)?

 

[Justinian]

No. I am not adept with VBA so I am not sure how to do that.

 

[JoeMo]

No. I am not adept with VBA so I am not sure how to do that.

Is the code you posted in your OP in a standard module in the workbook that you are using? Did you follow the step by step instructions to install the sheet code I posted in Post #2?

 

[Justinian]

Yes, it was but since it was not working, I deleted it when I saw your code, and yes, I followed the instructions.

 

[Justinian]

Yes, it was but since it was not working, I deleted it when I saw your code, and yes, I followed the instructions. I have since added it back in but it does not change negative numbers to red font. I am cycling through the code and all numbers are green. Even then, it only works when I run the macro and I am looking for code that runs automatically when cell values are changed I have to throw out my original code.

I want the code to run automatically so that each time a cell value changes, the corresponding textbox changes too. I hope that makes sense.

I received this code over the weekend and it correctly changes the font color depending on value but it only runs when I manually change each cell (A1:A12):

Private Sub Worksheet_Change(ByVal Target As Range)
' Update Shape TextBoxes with Inputs in Cells A1 to A12
' 7 April 2023
If Target.CountLarge > 1 Then Exit Sub
If Intersect(Target, Range("A1:A12")) Is Nothing Then Exit Sub
Dim i As Long
i = Target.Row
With ActiveSheet.Shapes("TextBox " & i)
.TextFrame.Characters.Text = Target.Value
If Left(.TextFrame.Characters.Text, 1) = "-" Then
.TextFrame.Characters.Font.Color = vbRed
Else
.TextFrame.Characters.Font.Color = vbGreen
End If
End With
End Sub