chart display using buttons

[paulinenj]

I have one sheet of data that refers to multiple other sheets of data. From that one sheet I am creating charts with drop down lists to select the cases the individual wants to use. I have 3 different charts that I need to make and I would like to put them on the same sheet, so as not to confuse the vlookup with different drop downs. I would like to make one chart visible at a time, using a button (or some other tool) to change the chart being displayed.

 

[acw]

Hi

Hope this may get you going. It is a commandbutton from the controls toolbar. In the code, there is only 1 chart on the sheet. If you change the index to refer to the specific chart you could also change the caption on the button to suit.

Private Sub CommandButton1_Click()
  Sheets("sheet1").ChartObjects(1).Visible = Not (Sheets("sheet1").ChartObjects(1).Visible)
  If Sheets("sheet1").ChartObjects(1).Visible Then
    res = "Hide"
  Else
    res = "Show"
  End If
  CommandButton1.Caption = "Chart1 " & res
End Sub

Tony

 

[paulinenj]

Thanks,

I am just getting familiar with this code....what does "res" refer to? and does "ChartObjects(1)" just refer to the chart on that page?

 

[acw]

HI

res is a variable that is used to house the Hide / Show text for the button caption.

chartobject(1) refers to the chart on that sheet. If there is only 1 chart, then it will select it. If there are multiple charts, then it will select one of them. That is why I suggested you get the name of the chart, and use it instead of the index.


Tony

 

[paulinenj]

ah....thank you!

 

[Jon Peltier]

Here's a page that links to a worksheet with a relevant example:

http://peltiertech.com/Excel/Charts/ChartTypeDropDown.html