Page 1 of 2 12 LastLast
Results 1 to 10 of 14

How do I hide a chart (with VBA?)

This is a discussion on How do I hide a chart (with VBA?) within the Excel Questions forums, part of the Question Forums category; Hi guys, I have a dashboard with 6 charts, one for each portfolio manager of a particular branch. Some branches ...

  1. #1
    Board Regular
    Join Date
    Sep 2011
    Location
    Cardiff, Wales
    Posts
    222

    Default How do I hide a chart (with VBA?)

    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.

  2. #2
    Board Regular
    Join Date
    Sep 2011
    Location
    Cardiff, Wales
    Posts
    222

    Default Re: How do I hide a chart (with VBA?)

    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?


    Code:
    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

  3. #3
    Board Regular
    Join Date
    Sep 2011
    Location
    Cardiff, Wales
    Posts
    222

    Default Re: How do I hide a chart (with VBA?)

    Bump.

  4. #4
    Board Regular
    Join Date
    Jun 2011
    Posts
    229

    Default Re: How do I hide a chart (with VBA?)

    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.

  5. #5
    Board Regular
    Join Date
    Sep 2011
    Location
    Cardiff, Wales
    Posts
    222

    Default Re: How do I hide a chart (with VBA?)

    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

  6. #6
    Board Regular
    Join Date
    Sep 2011
    Location
    Cardiff, Wales
    Posts
    222

    Default Re: How do I hide a chart (with VBA?)

    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.

    Code:
    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

  7. #7
    Board Regular
    Join Date
    Nov 2009
    Posts
    4,764

    Default Re: How do I hide a chart (with VBA?)

    Why not reduce the size of the chart to zero?:
    Code:
    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
    Last edited by p45cal; Jan 13th, 2012 at 07:53 AM.

  8. #8
    Board Regular
    Join Date
    Sep 2011
    Location
    Cardiff, Wales
    Posts
    222

    Default Re: How do I hide a chart (with VBA?)

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

  9. #9
    Board Regular
    Join Date
    Nov 2009
    Posts
    4,764

    Default Re: How do I hide a chart (with VBA?)

    Quote Originally Posted by Jed Shields View Post
    P45cal - the code is returning an error on the first With statement ...
    See adjusted message (code did not paste properly - a line got concatenated).

  10. #10
    Board Regular
    Join Date
    Sep 2011
    Location
    Cardiff, Wales
    Posts
    222

    Default Re: How do I hide a chart (with VBA?)

    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

    Code:
    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

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com