Hi There - hoping someone can help. I am a complete VBA novice and need help. I'd like the font color in my linked text box on chart named "GA" to match font color of $H$4 cell content in sheet "Data Input" when it changes (cell font color currrently changes based on conditional formatting). Have been googling forever and not getting anywhere - any and all help will be greatly appreciated!!
What's the logic behind the conditional formatting?
If posting code please use code tags, see here.
The conditional formatting is to correctly color the font of the Circle, Triangle or X Webding and is applied to cell $H$4 based on the entry in the drop down box in cell $H$3.
Not sure if this will answer it: On sheet named ("DataInput"), from a drop-down box in cell $H$3 the user will select the word "Green", "Yellow", or "Red". In cell $H$4 below that, user will see either a Green Circle, Yellow Triangle, or Red X (Webdings Font). The text box on chart (on separate sheet named "GA") is linked to the cell $H$4 on "DataInput" sheet. The text box is updating fine but font color is not matching the Webding Font color seen in cell $H$4.
The conditional formatting is to correctly color the font of the Circle, Triangle or X Webding and is applied to cell $H$4 based on the entry in the drop down box in cell $H$3.
Not sure if this will answer it: On sheet named ("DataInput"), from a drop-down box in cell $H$3 the user will select the word "Green", "Yellow", or "Red". In cell $H$4 below that, user will see either a Green Circle, Yellow Triangle, or Red X (Webdings Font). The text box on chart (on separate sheet named "GA") is linked to the cell $H$4 on "DataInput" sheet. The text box is updating fine but font color is not matching the Webding Font color seen in cell $H$4.
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
Last edited by Norie; 03-08-2015 at 09:33 AM.
Here ya go ... thanks for the assist !!
Last edited by rz9wqm; 03-08-2015 at 10:12 AM.
Try this, it goes in the Data Input sheet module which can be accessed by right clicking the sheet tab and selecting View Code.
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim chtObj As Object Dim txt As Object Dim lngCol As Long If Target.Address = "$H$3" Then Set chtObj = Chart4 Set txt = chtObj.Shapes("TextBox 4") 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
Perfect - you have no idea how much you've helped !!! Thank you !! Have a wonderful day !!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks