Conditional formatting a text box in a chart

January 21, 2010 at 10:29:34
Specs: Windows Vista
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!

See More: Conditional formatting a text box in a chart

Report •


#1
January 21, 2010 at 20:20:05
Yes you can create a macro attached to the onChange event for the cell, but seems like a lot of work for a small result.

Report •

#2
January 22, 2010 at 20:55:39
Hi,

The problem is that the cell J7 is using conditional formatting. Ordinary formatting gets transferred, but conditional formatting does not and even with a macro there is no simple way to find what conditional format has been applied.

If you use a macro you have to create a copy of the conditional formating formulas to work out which format is being applied, and then lookup the color for that conditional format. It's quite involved.

An alternative is to create a macro which acts like a conditional format, calculates a color based on the cell value and applies it to both the cell and the chart item. The cell has no conditional formatting applied, but both the cell and the chart item will change color based on the cell value (it doesn't have to be color it could be font, number format or borders etc.)

The macro is triggered by any change in cell values on the worksheet that contains cell J7. The macro tests that the changed cell is J7, then creates one of three colors - you could expand this to multiple colors based on the cell value, (it's not limited like conditional formats in Excel 2003).

The macro goes in the worksheet's Change event:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrHnd
'test if changed cell is our chart-linked cell
If Target.Address = "$J$7" Then
    Dim lngIdx As Long
    'test the value in the cell
    'and create a color value
    Select Case Target.Value
        Case Is < 10
        lngIdx = RGB(250, 10, 10)
        Case 10 To 19
        lngIdx = RGB(40, 40, 200)
        Case Else
        lngIdx = RGB(50, 150, 10)
    End Select
    'apply the color to the cell and to the chart object
    Worksheets("Sheet1").ChartObjects("Chart 2").Chart. _
        ChartTitle.Interior.Color = lngIdx
    Target.Interior.Color = lngIdx
End If
Exit Sub
'error handler
ErrHnd:
Err.Clear
End Sub

In this example the link is to the chart title in Chart 2. Hold down the Ctrl key and click your chart to get the name. Change the chart name and chart object as required.

Hope this is of interest.

Regards


Report •

#3
January 26, 2010 at 07:58:16
I'm not sure why, but I can't get it to work. Perhaps I should start by saying I am rather new to scripting. I followed your instructions to check the names and all. My chart is Chart2 and the data is on Sheet1 with no spaces. The macro copied and pasted as it is does not return any errors, but nothing happens. Am I supposed to place this against the cell on Sheet1 or assign a macro to textbox1. Oddest part is that all the text boxes (20 total) are called textbox1. I see in the script that a target address is identified, so I assume that I would just duplicate the script changning $J$7 to $J$8 and so on... Hate to be a pest, but can you offer more insight in helping me make this work?

Report •

Related Solutions

#4
January 26, 2010 at 08:00:33
Grok,
I realize it seems like a small result, but I have 20 boxes on 7 sheets to change the color on. I am thinking that automating the process rather than clicking text box by text box would speed things along and free my time for other projects.

Report •

#5
January 26, 2010 at 09:35:51
I have to agree with mblair about the advantages of doing "a lot of work for a small result"

I have a macro that does nothing more than input a formula in the activecell to set the cell equal to whatever is in Column C of the same row. e.g. I'll right click D45, slide down to the word Equal, release my mouse and =$C45 goes in the cell.

I have another one that sets the activecell and the one to the right of it equal to 1/2 the value of whatever is in Column C of the same row. e.g. =$C52*.05

I have many more macros that do very, very simple things, but I sometimes do these simple things hundreds of times a month. It's so much easier to right-click a cell, slide down to the word "Blend" and have it insert the 2 formulae than to manually type =$Cxx*.05 into 2 cells hundreds of times a month.

Once I find myself repeating a task on a regular basis, I automate it with a macro and either add a right-click menu item or a toolbar button with an image that reminds me of what the macro does.


Report •

#6
January 27, 2010 at 05:09:44
Hi,

No problem.

The code is attached to the worksheet that contains the linked cell, which is Sheet1.

Go to Sheet1.
Right click on the name tab for Sheet1 at the bottom of the Excel window.
Select View Code.
Paste the code into the code window (typically on the right side). It has two drop-down boxes at the top.

After pasting the code, click inside the code and the lefthand drop down will show 'Worksheet' and the righthand drop-down will show 'Change'
This means that the code will run whenever any cell on the worksheet changes.

Now make some changes to the code to match you specific situation:
1. The linked cell.
This line:
If Target.Address = "$D$9" Then
identifies the linked cell. The cell address must have the $ signs.
If cell D9 triggers the change event, the Target variable will equal $D$9. So the rest of this subroutine code only runs if it was cell D9 that changed.

2. The chart object
These are the lines for a Text Box inside a chart on sheet 2
Worksheets("Sheet2").ChartObjects("Chart 1").Chart.Shapes("Text Box 1").Fill.Visible = msoTrue
Worksheets("Sheet2").ChartObjects("Chart 1").Chart.Shapes("Text Box 1").Fill.ForeColor.RGB = lngIdx
The first line makes the color visible and the second line sets the color. Technically you don't need to set the 'visible' variable to true for every change, but it is simpler to just include it.

I see that you have 20 text boxes - are these all inside one chart or are they in different charts.

Inside charts, text boxes are numbered by chart, so 20 charts with one text box each will each be named "Text Box 1". They are identified by Chart name + Text Box number

Hope this helps

Please ask again if you are still having difficulty

Regards


Report •

#7
January 28, 2010 at 04:02:54
Hi,

You let me know that you have the following in your code and it is not working:
Worksheets("Sheet1").ChartObjects("Chart2").Chart.Shapes("TextBox41").Fill.ForeColor.RGB = lngIdx
'Worksheets("Sheet1").ChartObjects("Chart2").Chart2.Shapes("TextBox41").Fill.Visible = msoTrue
'Worksheets("Sheet1").ChartObjects("Chart2").Chart2.Shapes("TextBox41").Fill.ForeColor.RGB = lngIdx

1. You have commented out the line that makes the fill visible.
This line must be active:
Worksheets("Sheet1").ChartObjects("Chart2").Chart2.Shapes("Text Box 41").Fill.Visible = msoTrue

2. The name of the text box looks wrong - I can't be sure, but usually text box names are "Text Box 41" and not "TextBox41"

3. Make sure that the Chart name and worksheet name are correct. Is the Chart on Sheet1. Also on the worksheet with the chart, hold down the Control key and click the edge of the chart. The chart's name will appear in the name box at top left, above the row and column letters. Ensure that both are correct in the code.

If this does not work, go to the code and place a break point against the line:
If Target.Address = "$J$7" Then
In the code window there is a border down the left side, to the left of the code itself. Click in this space next to the line and a dot will appear and the line will be highlighted.

Now change the value of cell J7.
The code window should become active, with the line of code shown in yellow.
Use the f8 function key to single step through the code and see if it completes and exits at the Exit Sub line or whether it jumps to the Error handler.
If it jumps to the error handler, note the last line before it jumped, and post the results.

Regards


Report •

#8
January 28, 2010 at 08:50:47
I truly appreciate all the help! After much tinkering, I was able to make it work, but not in the original spreadsheet. I created a new spreadsheet and placed the following script into it:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrHnd
'workbook and chart on seperate tabs
'test if changed cell is our chart-linked cell
If Target.Address = "$J$7" Then
Dim lngIdx As Long
'test the value in the cell
'and create a color value
Select Case Target.Value
Case Is < 0.06
'Green
lngIdx = RGB(50, 150, 10)
Case 0.06 To 0.15
'Yellow
lngIdx = RGB(255, 255, 0)
Case Else
'Red
lngIdx = RGB(250, 10, 10)
End Select
'apply the color to the cell and to the chart object
Worksheets("Sheet2").ChartObjects("Chart 1").Chart.Shapes("TextBox 1").Fill.Visible = msoTrue
Worksheets("Sheet2").ChartObjects("Chart 1").Chart.Shapes("TextBox 1").Fill.ForeColor.RGB = lngIdx
Target.Interior.Color = lngIdx
End If
Exit Sub
'error handler
ErrHnd:
Err.Clear
End Sub

But how do I make this work on a range of cells that correlate to a range of text boxes J7 through J17 and K7 through K17? I tried adding the code again, changing the subroutine’s title to Worksheet_Change1, lngIdx to lngIdx_1 and of course, the text box to 2 and it didn’t like it. I need to be able to duplicate this effort over the 20 text boxes that are present in the chart. Do you have any ideas on how to work that?


Report •

#9
January 28, 2010 at 09:10:45
I figured out how to make more than one cell work from this code.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrHnd
'workbook and chart on seperate tabs
'test if changed cell is our chart-linked cell
If Target.Address = "$J$7" Then
Dim lngIdx As Long
'test the value in the cell
'and create a color value
Select Case Target.Value
Case Is < 0.06
'Green
lngIdx = RGB(50, 150, 10)
Case 0.06 To 0.15
'Yellow
lngIdx = RGB(255, 255, 0)
Case Else
'Red
lngIdx = RGB(250, 10, 10)
End Select
'apply the color to the cell and to the chart object
Worksheets("Sheet2").ChartObjects("Chart 1").Chart.Shapes("TextBox 1").Fill.Visible = msoTrue
Worksheets("Sheet2").ChartObjects("Chart 1").Chart.Shapes("TextBox 1").Fill.ForeColor.RGB = lngIdx
Target.Interior.Color = lngIdx
End If
If Target.Address = "$J$6" Then
Dim lngIdx_1 As Long
'test the value in the cell
'and create a color value
Select Case Target.Value
Case Is < 0.06
'Green
lngIdx_1 = RGB(50, 150, 10)
Case 0.06 To 0.15
'Yellow
lngIdx_1 = RGB(255, 255, 0)
Case Else
'Red
lngIdx_1 = RGB(250, 10, 10)
End Select
'apply the color to the cell and to the chart object
Worksheets("Sheet2").ChartObjects("Chart 1").Chart.Shapes("TextBox 2").Fill.Visible = msoTrue
Worksheets("Sheet2").ChartObjects("Chart 1").Chart.Shapes("TextBox 2").Fill.ForeColor.RGB = lngIdx_1
Target.Interior.Color = lngIdx_1
End If

Exit Sub
'error handler
ErrHnd:
Err.Clear
End Sub


Report •

#10
January 28, 2010 at 10:52:30
Hi,

The first issue is to have this macro respond to changes in a range of cells rather than just one cell.

If all your linked cells are in a block
J7 to K17, then test if the Target is anywhere in this range.

The following code will test for a change in any of these cells:

Dim objChartLink As Object
Set objChartLink = Application.Intersect(Target, Range("J7:K17"))
If Not objChartLink Is Nothing Then
... code to create format  etc. in here ...
Dim lngIdx As Long
...
End If

The next issue will be to identify which cell links to which chart and text box.

If there is a logical arrangement of the cells in the range, then we might be able to calculate which chart and which text box to change based on the cell address. This would make the solution easier to manage long-term, rather than hard-coding the links.
If charts on Sheet 2 were in column J and Charts in Sheet 3 were in column K etc. etc.

Another way might be to use cell comments, and the code reads the cell comments which contain worksheet, chart and text box information.

Let me know what you think might work.

Regards


Report •

#11
February 1, 2010 at 08:54:14
I think it would be better to write the code as a range rather than an individual set of code per text box/cell. The cells associate to boxes sequentially, J7 = TB90, J16 = TB99, K7 = TB54, K16 = 63. But how will the system know which textbox to associate?

A few modifications to the original code have happened... so here is the code currently inplace.


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

'Variable declarations

Dim SVUpperLimit, SVLowerLimit
Dim CVUpperLimit, CVLowerLimit
Dim TBVisible 'For Text Box visibility
SVUpperLimit = -0.08 '-8%
SVLowerLimit = -0.2 '-20%
CVUpperLimit = -0.06 '-6%
CVLowerLimit = -0.15 '-15%
TBVisible = msoTrue 'Chart and Format will appear


'Schedule Variance

'test if changed cell is our chart-linked cell
If Target.Address = "$J$7" Then
Dim lngIdx As Long
'test the value in the cell
'and create a color value
Select Case Target.Value

Case ""
'Gray
lngIdx = RGB(221, 217, 195)
TBVisible = msoFalse

Case Is > SVUpperLimit
'Green
lngIdx = RGB(50, 150, 10)

Case SVLowerLimit To SVUpperLimit
'Yellow
lngIdx = RGB(255, 255, 0)

Case Is < SVLowerLimit
'Red
lngIdx = RGB(250, 10, 10)

End Select

'apply the color to the cell and to the chart object & allows text boxes to be visible.
Worksheets("Chart").ChartObjects("Chart 1").Chart.Shapes("TextBox 90").Fill.Visible = TBVisible
Worksheets("Chart").ChartObjects("Chart 1").Chart.Shapes("TextBox 90").Fill.ForeColor.RGB = lngIdx
Worksheets("Chart").ChartObjects("Chart 1").Chart.Shapes("TextBox 90").Visible = TBVisible
Target.Interior.Color = lngIdx
End If

This code between If Target and End If repeats for each cell.

By the way, this works very well, unless you highlight more than one cell and delete the cell entries. If you do that, the cells do not return to a non-visible state.


Report •

#12
February 2, 2010 at 05:50:18
Hi,

Yes, your biggest issue is how to know what text box each cell is associated with.

From your example it appears that although the cells involved are sequential, there is no simple relationship between a series of cells and the text box name/number.

One way would be to have a comment added to each cell. The comment contains the sheet, chart and text box information.

The code then uses the cell's comments to generate the text box address

In cell D9 which is linked to Text Box 2 in Chart 1 on Sheet 2,
I added a comment (Menu-Insert-Comment)
I removed the default user name and entered:
Sheet2
Chart 1
Text Box 2

I used Ctrl+Enter for the new lines
I hid the comment using right-click Hide Comment

In the code I used this:
after Dim lngIdx as Long:

    Dim strChar As String
    Dim strCmntArry(3) As String
    Dim m As Integer
    Dim n As Integer

and after End select:

    'get the information from the cell comments
    'set array counter
    m = 1
    'loop through text  starting after fixed text "Text Box: "
    For n = 11 To Len(Target.Comment.Shape.AlternativeText)
        'get a single character
        strChar = Mid(Target.Comment.Shape.AlternativeText, n, 1)
        'test if new line char
        If Asc(strChar) = 10 Then
            'move to next array element
            m = m + 1
            Else
            'add character
            strCmntArry(m) = strCmntArry(m) + strChar
        End If
    Next n
    'apply the color to the cell and to the chart object
    Worksheets(strCmntArry(1)).ChartObjects(strCmntArry(2)). _
        Chart.Shapes(strCmntArry(3)).Fill.Visible = msoTrue
    Worksheets(strCmntArry(1)).ChartObjects(strCmntArry(2)). _
        Chart.Shapes(strCmntArry(3)).Fill.ForeColor.RGB = lngIdx
    Target.Interior.Color = lngIdx

Regards


Report •

#13
February 4, 2010 at 07:39:38
Rather than use comment boxes, we went a different way to shorten the code and link the cell to textbox:
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

Unfortunately, this still doesn't change the visibility of the boxes on the chart if more than one cell is selected and deleted. I can remove the formatting from the worksheet but I can't find the right command to send visible = msoFalse to more than one text box at a time.

Any thoughts?


Report •

#14
February 4, 2010 at 07:56:17
I have not been following this thread too closely, so I don't know if this wiil help...

Can you wrap your code with a check to see if more than one cell is selected within the range where changes might be made and tell the user that he can't do that?

The following code checks the range that is selected and if it intersects, e.g. J7:K16, in any manner, then it counts the number of selected cells and pops up a message box if more than 1 is selected.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 If Not Intersect(Target, Range("J7:K16")) Is Nothing Then
  If Selection.Cells.Count > 1 Then _
     MsgBox "You May Only Select 1 Cell At A Time Within This Range"
     Exit Sub
 End If
'Your code...
End Sub



Report •

#15
February 4, 2010 at 08:01:04
DD, I was wondering it that was at all possible. I'd rather allow all the data to be dumped, but if there is no way to do it, this is the route I was going to seek next. Thanks!

Report •

#16
February 4, 2010 at 08:14:48
Hi,

Not sure that I properly understand the problem.

I did come up with this routine that removes the visible background on all Text Boxes in Charts.

Sub TBClear()
Dim objChartObj As ChartObject
Dim objTxtBx As Shape

For Each objChartObj In Worksheets("Sheet2").ChartObjects()
    For Each objTxtBx In objChartObj.Chart.Shapes()
        objTxtBx.Fill.Visible = msoFalse
    Next objTxtBx
Next objChartObj
End Sub

Perhaps you can modify this to suit.

Regards


Report •

#17
February 4, 2010 at 08:46:48
Another option is to decide what different actions should occur if one cell is selected/changed or if multiple cells are selected/changed, based the Cells.Count result.

You don't have to prevent the user from deleting all of the data at once...you could have the code take specific actions when that happens.


Report •

#18
March 4, 2010 at 13:17:29
DD, I'd be curious to see what you had in mind with your last post.

Report •

#19
March 4, 2010 at 13:44:08
You expect me to remember what I had in mind a month ago?

Wow! You have more faith in my brain cells than I do!

I've reviewed the thread and I think I know what I meant...

In Response #14 I offered some code that would prevent the user from selecting more than one cell in a specified range, because that seemed to be causing you problems.

What I probably meant in Response #17 was that instead of telling the user that he can't select more than one cell, you could monitor which/how many cells they selected and customize what they are allowed to do based on what the macro finds.

i.e. If the user selects a range that you are comfortable with them deleting all at once then allow them to delete it. If they select a range that causes your code to blow up, then prevent that selection.

I guess I was just suggesting that you are not limited to only the option I offered in Response #14.

Hope that helps.


Report •


Ask Question