How do I hide a chart (with VBA?)

[Jed Shields]

Hi guys,

I have a dashboard with 6 charts, one for each portfolio manager of a particular branch. Some branches have the maximum 6 PMs, some only have 1. I select the branch using a dropdown list and the charts update for the relevant PMs. In the branches where I have less than 6 PMs, how can I hide the charts that will appear blank?

Cheers,

Jed.

 

[Jed Shields]

Right, I've made some progress. I'm faking hiding the chart by simply turning all the lines off and changing the axis font from black to white (to match the white background). I've got it all working but it falls over when it gets to the font colour. I've recorded a macro doing this and simply cut and paste it into the rest of my code. I'm not sure why it's failing. Any ideas?

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$20" Then

If Sheet4.Range("A20") = "0" Then
    ActiveSheet.ChartObjects("Chart 9").Activate
    ActiveChart.Axes(xlValue).Select
    Selection.Format.Line.Visible = msoFalse
    ActiveChart.ChartArea.Select
    ActiveChart.PlotArea.Select
    Selection.Format.Line.Visible = msoFalse
    ActiveChart.Axes(xlValue).MajorGridlines.Select
    Selection.Format.Line.Visible = msoFalse
    ActiveChart.ChartArea.Select
    ActiveChart.PlotArea.Select
    ActiveChart.Axes(xlCategory).Select
    Selection.Format.Line.Visible = msoFalse
    ActiveChart.Axes(xlValue).Select

ActiveChart.Axes(xlValue).Select
    With Selection.Format.TextFrame2.TextRange.Font             'Fails here
        .BaselineOffset = 0
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorBackground1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
    End With
    ActiveChart.Axes(xlCategory).Select
    With Selection.Format.TextFrame2.TextRange.Font
        .BaselineOffset = 0
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorBackground1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
    End With

    ActiveChart.PlotArea.Select
    ActiveChart.ChartArea.Select
    ActiveSheet.Shapes("Chart 9").Line.Visible = msoFalse
    Range("A22").Select
    
    End If
   
   
    If Sheet4.Range("A20") = "1" Then
        ActiveSheet.ChartObjects("Chart 9").Activate
        ActiveChart.Axes(xlValue).Select
        Selection.Format.Line.Visible = msoTrue
        Range("A21").Select
        ActiveCell.FormulaR1C1 = "Show"
        Range("A22").Select
    End If
End If
End Sub

 

[Jed Shields]

Bump.

 

[cbrf23]

Do you add the chart programmatically or is it an existing object/sheet? From your code above it looks like an existing sheet.

What I would do, is delete the old chart and add a new one. Then, if your chart is going to appear blank, you just dont add it in.

Let me know if you need help with the code.

 

[Jed Shields]

Yes it's in an existing sheet. I've got 6 charts, one for each potential Portfolio Manager. The sheet also has some navigation buttons to move around the dashboard (other sheets), plus a drop down list to select the Branch.

I have 6 sets of data tables to the right, one for each potential PM. If there is no PM then the data is blank.

So if I select Branch 1, which has 6 PMs, then all 6 data tables and therefore 6 charts are filled.

However, If I pick Branch 2, which only has 1 PM, then only the first data table has data. Chart 1 shows the data that exists, the other charts are blank. I thought I could tie in a binary toggle that would specify if a table has data or not. If it does then the chart format is standard. If not, the it needs to format the table so that the axis lines disappear and the axis font turns white.

I'm not sure what code would be needed to just add or delete an entire chart though? Any help would be much appreciated

 

[Jed Shields]

The code works perfectly apart from changing the font colour. It falls over at the code, below. If I rem this code out then the rest of the code works perfectly.

Is there anything obviously wrong with the code? I've simply copied it from a macro recording, so I'd have thought it would work.

ActiveChart.Axes(xlCategory).Select
    With Selection.Format.TextFrame2.TextRange.Font
        .BaselineOffset = 0
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
    End With

 

[p45cal]

Why not reduce the size of the chart to zero?:

Sub blah()
With ActiveSheet.ChartObjects("Chart 9")  
  h = .Height  'retain original height value
  w = .Width  'retain original width value
  .Height = 0
  .Width = 0
  MsgBox "chart not visible"
  'restore dimensions:
  .Height = h
  .Width = w
End With
End Sub

 

[Jed Shields]

P45cal - the code is returning an error on the first With statement ...

 

[p45cal]

P45cal - the code is returning an error on the first With statement ...

See adjusted message (code did not paste properly - a line got concatenated).

 

[Jed Shields]

Okay. It wouldn't work until I rem'd out the Restore Dimensions lines (I assume it was setting to 0, then returning to original height?)

Anyway, when it sets it to 0 height there's still a telltale sign that something is there ie I can see the bottom part of the axis text. Grrr. Close but no cigar

Sub blah()
With ActiveSheet.ChartObjects("Chart 9")
  h = .Height  'retain original height value
  w = .Width  'retain original width value
  .Height = 0
  .Width = 0
  MsgBox "chart not visible"
  'restore dimensions:
  '.Height = h
  '.Width = w
End With
End Sub