Text box linked to cell value

mblair Posts 1 Registration date Wednesday January 20, 2010 Status Member Last seen January 21, 2010 - Jan 21, 2010 at 02:52 PM
 mblair - Mar 4, 2010 at 03:23 PM
I have a text box placed in a chart that links to another sheet/cell for its content. The value is a percentage. I can format the source text using conditional format, but the formatting doesn't carry forward into the text box. Is there a way to script/macro or use if/then for the value of a text box to change the font color? The text box is linked simply with "=Data!$J$7" ? Any help would be greatly appreciated!

3 responses

Hi mblair,

Try this : FormatPercent(Data!$J$7,0)

First remove the formula from the TextBox and make sure that it is just text or blank

If the Chart is in its own Sheet eg "Chart1" then:

 Insert into a macro:

          dim A$
          A = ActiveSheet.Range("J7").Value


          With ActiveSheet.Shapes("TextBox").TextFrame
                 .Characters.Insert (A)
                 .Characters.Font.Color = RGB(0, 150, 0)
          End With

     Note: You'll need to 

     replace "Chart1" with what ever is on the Chart Sheet Tab

      replace "TextBox" with the name of the specific TextBox you want to format.  You can find this out by hovering the mouse over the text box and the name will pop up in a yellow box.
     Modify RGB function with the (R,G,B) where R G and B are integers 0 to 255 to define the color eg, pure Red would be (255,0,0)

If "Chart1" is embedded in a Sheet with cells eg "Sheet1" then:

 Insert into a macro:

    Dim A$
    A = ActiveSheet.Range("J7").Value

    ActiveSheet.ChartObjects("Chart 1").Activate
     With ActiveChart.Shapes("TextBox").TextFrame
         .Characters.Insert (A)
         .Characters.Font.Color = RGB(0, 150, 0)
     End With

Hope this helps!
I cross posted this to another site and with a little back and forth came up with the code posted below. This works great as long as you are adding data or removing data one block at a time. My issue now is that if you select more than one block at a time and delete the data, the corresponding text boxes retain their previous state of color but without the numeric value. I'm open for suggestions on a way to preform a check of all cells to return them to a non-visible state...??

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrHnd

'Variable declarations

Dim SVUpperLimit, SVLowerLimit
Dim CVUpperLimit, CVLowerLimit

Dim TBVisible
Dim WSName
Dim ChartName
Dim TBName
Dim TBColor

Dim Red, Yellow, Green, WSCellFillColor

Dim SVColumn, CVColumn, RowStart, RowEnd

SVColumn = 10 'column J
CVColumn = 11 'column K
RowStart = 7
RowEnd = 16

'Process only the SV and CV cells in the worksheet
If ((Target.Column = SVColumn Or Target.Column = CVColumn) And (Target.Row >= RowStart And Target.Row <= RowEnd)) Then
SVUpperLimit = -0.08 '-8%
SVLowerLimit = -0.2 '-20%
CVUpperLimit = -0.06 '-6%
CVLowerLimit = -0.15 '-15%

Red = RGB(250, 10, 10)
Yellow = RGB(255, 255, 0)
Green = RGB(50, 150, 10)
WSCellFillColor = RGB(255, 204, 153)

WSName = "Chart"
ChartName = "Chart 1"

TBVisible = msoTrue
TBColor = WSCellFillColor

'Set which text box to update

Select Case Target.Address
'Schedule Variance text boxes
Case Is = "$J$7": TBName = "TextBox 90"
Case Is = "$J$8": TBName = "TextBox 91"
Case Is = "$J$9": TBName = "TextBox 92"
Case Is = "$J$10": TBName = "TextBox 93"
Case Is = "$J$11": TBName = "TextBox 94"
Case Is = "$J$12": TBName = "TextBox 95"
Case Is = "$J$13": TBName = "TextBox 96"
Case Is = "$J$14": TBName = "TextBox 97"
Case Is = "$J$15": TBName = "TextBox 98"
Case Is = "$J$16": TBName = "TextBox 99"
'Cost Variance text boxes
Case Is = "$K$7": TBName = "TextBox 54"
Case Is = "$K$8": TBName = "TextBox 55"
Case Is = "$K$9": TBName = "TextBox 56"
Case Is = "$K$10": TBName = "TextBox 57"
Case Is = "$K$11": TBName = "TextBox 58"
Case Is = "$K$12": TBName = "TextBox 59"
Case Is = "$K$13": TBName = "TextBox 60"
Case Is = "$K$14": TBName = "TextBox 61"
Case Is = "$K$15": TBName = "TextBox 62"
Case Is = "$K$16": TBName = "TextBox 63"
End Select

Select Case Target.Column
Case Is = SVColumn 'Schedule Variance
Select Case Target.Value 'Check value against stop light values and set color of text box fill
Case "" 'blank cell
TBVisible = msoFalse 'make invisible
Case Is > SVUpperLimit
TBColor = Green
Case SVLowerLimit To SVUpperLimit
TBColor = Yellow
Case Is < SVLowerLimit
TBColor = Red
End Select
Case Is = CVColumn 'Cost Variance
Select Case Target.Value 'Check value against stop light values and set color of text box fill
Case "" 'blank cell
TBVisible = msoFalse 'make invisible
Case Is > CVUpperLimit
TBColor = Green
Case CVLowerLimit To CVUpperLimit
TBColor = Yellow
Case Is < CVLowerLimit
TBColor = Red
End Select
End Select

'update chart text box
Worksheets(WSName).ChartObjects(ChartName).Chart.Shapes(TBName).Fill.ForeColor.RGB = TBColor
Worksheets(WSName).ChartObjects(ChartName).Chart.Shapes(TBName).Visible = TBVisible

'update worksheet cell
Target.Interior.Color = TBColor
End If

Exit Sub
'error handler
End Sub