Conditionally show/hide charts

[3link]

I have approximately 12 charts in a spreadsheet I'm designing. I want to be able to display all of the charts within one designated area of a certain sheet. Each of the 12 charts will be available (1 at a time) upon conditions (likely a drop down menu selection). I thought this would be possible using the this technique:

http://chandoo.org/wp/2008/11/05/select-show-one-chart-from-many/

The idea is that you are able to display the image of each chart onto a single picture that changes conditionally. I attempted to test this before applying my own charts. I kept getting a reference error for the formula. I have double-checked and everything in the formula seems to be written correctly. Secondly, I can't seem to manipulate the formula bar for my selected image. Thus, even if I could get the formula working, I wouldn't be able to assign the named range to my image.

Has microsoft updated Excel to prevent people from doing this? My suspicion is "no" since the demo uploaded seems to be working.

Is there a way to hide charts conditionally in VBA if all else fails?

 

[3link]

K. I figured it out. (Actually, I borrowed from this guy.)

Anyway, here's my sample.

Here's the code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim SelectedChart As Range
Dim blnVisible As Boolean


 Set SelectedChart = Range("G5")
    
    blnVisible = SelectedChart.Value = ""
    
' Make first all charts invisible
    ActiveSheet.ChartObjects("Test1").Visible = blnVisible
    ActiveSheet.ChartObjects("Test2").Visible = blnVisible
    ActiveSheet.ChartObjects("Test3").Visible = blnVisible


    If Not blnVisible Then ActiveSheet.ChartObjects(SelectedChart).Visible = True

End Sub

My old code made every graph on the sheet invisible except for the selected graph. This code will make every graph on your list invisible except for the one on your list. Essentially, you want your list to comprise of every graph that you want to be invisible. In my case, it was Test1, Test2 and Test3. Test4 and Test5 remain visible at all times.

Let me know if you need anything more help.

Thanks

 

[KiaOrana]

It works perfectly! Thank you so much 3link, you have no idea of how much I appreciated your help!

 

[kaldaru]

Hello!




I know it is an old thread, but my problem is very similar to Kia's. As obvious at it seems, I'm a pure VBA newbie!


I am building a dashboard and I need to put lots of graph in it.


That is why I used VBA to be able to show/hide some of them in a part of my dashboard.


I used simple VBA lines to do so.


Now I would like to repeat the same opeation for another part of my dashboard. I face the same worksheet_change
problem and I don't really know how to combine two events.






Thanks in advance!


Hugo






PS:This is basically what does my simple line is to show/hide graphs:






Private Sub Worksheet_Change(ByVal Target As Range)
Dim SelectedChart As Range
Dim blnVisible As Boolean




Set SelectedChart = Range("G5")

blnVisible = SelectedChart.Value = ""

' Make first all charts invisible
ActiveSheet.ChartObjects("Test1").Visible = blnVisible
ActiveSheet.ChartObjects("Test2").Visible = blnVisible
ActiveSheet.ChartObjects("Test3").Visible = blnVisible




If Not blnVisible Then ActiveSheet.ChartObjects(SelectedChart).Visible = True


End Sub

 

[3link]

I'm not sure I understand your problem.

1. What is a dashboard? Do you mean spreadsheet? So you tried to use the same code in another sheet of the spreadsheet?
2. If I understand correctly, you were able to use this code on one sheet in your spreadsheet. Then you tried to use it on another and it didn't work? Or are you trying to use the same code twice on a single sheet?

 

[kaldaru]

Hi!

Sorry My main language isn’t English so maybe my question wasn’t so clear!

Also I am sorry my first intention was to post this question in that thread http://www.mrexcel.com/forum/excel-questions/611379-multiple-worksheet_change-sheet.html . It would have been more clear that way.

I'm not sure I understand your problem.

1. What is a dashboard? Do you mean spreadsheet? So you tried to use the same code in another sheet of the spreadsheet?

When I say dashboard I mean an Business Dashboard. With Charts and graph.

I2. If I understand correctly, you were able to use this code on one sheet in your spreadsheet. Then you tried to use it on another and it didn't work? Or are you trying to use the same code twice on a single sheet?

I want my dashboard to fit in one excel sheet. Wich is why I need the combining VBA code for one sheet only.

 

[3link]

Hi!

Sorry My main language isn’t English so maybe my question wasn’t so clear!

Also I am sorry my first intention was to post this question in that thread http://www.mrexcel.com/forum/excel-questions/611379-multiple-worksheet_change-sheet.html . It would have been more clear that way.





When I say dashboard I mean an Business Dashboard. With Charts and graph.





I want my dashboard to fit in one excel sheet. Wich is why I need the combining VBA code for one sheet only.

I have no idea what a business dashboard is. Is that part of excel? I've never heard of it. Maybe it goes by another name? Do you want to just upload a copy of your spreadsheet so I can see what you're talking about?

 

[kaldaru]

I have no idea what a business dashboard is. Is that part of excel? I've never heard of it. Maybe it goes by another name? Do you want to just upload a copy of your spreadsheet so I can see what you're talking about?

Well you know, a sales/business dashboard! Just like in that link : http://img.chandoo.org/v/l/sales-data-dashboard-martin-1-excel.png




Unfortunately i can't upload mine here because of confidentiality agreement with my company..




Anyhoo i will just give an exemple sheet wich you may remember because you gave it to help solve
Kia's problem a year ago.

https://docs.google.com/file/d/0B3X1RupOnokQNjk4ZmU2OGQtNmU4ZC00M2U2LTg5MjQtZjZlZTQ1NmI3MmRk/edit?pli=1




In that spreadsheet you can see the combo box that controls 3 different graphs thanks to that VBA code.




Now what if you want to add another combo box in the same sheet that could control totally different graphs and so on?


That is where my problem is: combining VBA codes to be able to have 2(or more) totally independent combo box that controls
totally independent graphs!


Thanks again for your time

 

[3link]

Well you know, a sales/business dashboard! Just like in that link : http://img.chandoo.org/v/l/sales-data-dashboard-martin-1-excel.png




Unfortunately i can't upload mine here because of confidentiality agreement with my company..




Anyhoo i will just give an exemple sheet wich you may remember because you gave it to help solve
Kia's problem a year ago.

https://docs.google.com/file/d/0B3X1RupOnokQNjk4ZmU2OGQtNmU4ZC00M2U2LTg5MjQtZjZlZTQ1NmI3MmRk/edit?pli=1




In that spreadsheet you can see the combo box that controls 3 different graphs thanks to that VBA code.




Now what if you want to add another combo box in the same sheet that could control totally different graphs and so on?


That is where my problem is: combining VBA codes to be able to have 2(or more) totally independent combo box that controls
totally independent graphs!


Thanks again for your time

Ah yes. That's easy. You basically just repeat the code, but make new names for the variables. See here:

Private Sub Worksheet_Change(ByVal Target As Range)Dim SelectedChart As Range
Dim blnVisible As Boolean


' Pick up the selected chart name in AF20 depending on CellLink of the ComboBox
 Set SelectedChart = Range("G5")
    
    blnVisible = SelectedChart.Value = ""
    
' Make first all charts invisible
    ActiveSheet.ChartObjects("Test1").Visible = blnVisible
    ActiveSheet.ChartObjects("Test2").Visible = blnVisible
    ActiveSheet.ChartObjects("Test3").Visible = blnVisible




' Make only the selected chart visible
    If Not blnVisible Then ActiveSheet.ChartObjects(SelectedChart).Visible = True




Dim SelectedChart2 As Range
Dim blnVisible2 As Boolean


' Pick up the selected chart name in AF20 depending on CellLink of the ComboBox
 Set SelectedChart = Range("G6")
    
    blnVisible = SelectedChart.Value = ""
    
' Make first all charts invisible
    ActiveSheet.ChartObjects("Test7").Visible = blnVisible
    ActiveSheet.ChartObjects("Test8").Visible = blnVisible
    ActiveSheet.ChartObjects("Test9").Visible = blnVisible




' Make only the selected chart visible
    If Not blnVisible Then ActiveSheet.ChartObjects(SelectedChart).Visible = True


End Sub

Notice how I named "selectchart2" and "binvisible2" this time around so VBA can distinguish those variables from those in the other selection.

Works like a charm. See here: https://docs.google.com/file/d/0B3X1RupOnokQMGJRc0cxYXNSUkE/edit

 

[kaldaru]

Thanks a lot I will try it!

 

[jahmed59]

Hi

Im hoping you can really help

I have approx 40 charts which refer to data collected from different cities. I would like to show and hide conditionally with VBA I have used the following code successfully


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Then
Zach.ChartObjects.Visible = False
On Error Resume Next
ChartObjects(Target.Value).Visible = True
End If
End Sub


But.... I have four graphs which have the same chart name i.e from the same city but they are showing two different types of data.
I have named both charts with the same name but when i run the code only one chart appears... how do I make 2 appear for the same chart name - I would like to see a comparison.


All help is greatly appreciated