Conditional formatting a text box in a chart

Humar 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

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?

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

Humar 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

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.

Humar 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

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?

DerbyDad03 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

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!

Humar 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

DerbyDad03 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.

DD, I'd be curious to see what you had in mind with your last post.

DerbyDad03 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.