That's actually what I needed as it tells me that we can use the change event of the sheet the chart is on to trigger the code to change the font colour on the textbox on the chart.
Can you upload an example workbook with an example chart?
Click on GO ADVANCED and use the paperclip icon to open the upload window.
In the meantime here's some sample code.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim chtObj As ChartObject
Dim txt As Object
Dim lngCol As Long
If Target.Address = "$H$3" Then
Set chtObj = Me.ChartObjects(1)
Set txt = chtObj.Chart.Shapes("TextBox 2")
Select Case Target.Value
Case "Red"
lngCol = RGB(255, 0, 0)
Case "Green"
lngCol = RGB(0, 255, 0)
Case "Yellow"
lngCol = RGB(255, 255, 0)
End Select
txt.OLEFormat.Object.Font.Color = lngCol
End If
End Sub
Bookmarks