Conditionally show/hide charts

3link · Jan 31, 2011

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?

KiaOrana · Jan 29, 2012

Hi,

I am having exactly the same issue as you describe. Have you found a work around?

3link · Jan 29, 2012

KiaOrana said:
Hi,

I am having exactly the same issue as you describe. Have you found a work around?

Yeah I did, but I had use to VBA.

On the sheet I used to display the graphs, I put the following code:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$2" Then
        ActiveSheet.ChartObjects.Visible = False
        On Error Resume Next
        ChartObjects(Target.Value).Visible = True
End if
End Sub

Range A2 contains a dropdown list with the names of each graph I want to display. All of the graphs are on the sheet. Each graph is named exactly as the name appears on the dropdown list. (To name a graph, click on the graph, select the "layout" option at the top of excel and type the name of the graph under "properties"). Essentially, the above code will hide every graph on your sheet except for the graph that shares the name that is displayed in A2.

Hope that helps.

KiaOrana · Jan 29, 2012

Thanks a lot mate! I'll give it a go

KiaOrana · Jan 29, 2012

You're a legend 3link! it works like a charm! Thanks a lot

KiaOrana · Jan 29, 2012

Help!

Hi 3link,

I think I have done something terrible and really need your help. I applied your technique to a dashboard I have been working on for my boss. I didn't realise (didn't think I should say) that by running this macro, all the graphs will be hidden, including the dozen of graphs I have been working on for the past four week!!!

I have lost everything (except the graph "Monday" which works like a charm thanks to the query)...

How can I make everythign show again?

Please I hope you can help me
Thanks

3link · Jan 30, 2012

Re: Help!

KiaOrana said:
Hi 3link,

I think I have done something terrible and really need your help. I applied your technique to a dashboard I have been working on for my boss. I didn't realise (didn't think I should say) that by running this macro, all the graphs will be hidden, including the dozen of graphs I have been working on for the past four week!!!

I have lost everything (except the graph "Monday" which works like a charm thanks to the query)...

How can I make everythign show again?

Please I hope you can help me
Thanks

Sorry for the delayed response. For some reason I didn't get notification emails for your latest replies.

Anyway, yes. Your graphs are not "gone". They are just hidden. To unhide them, change the code to this:

Rich (BB code):

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$2" Then
        ActiveSheet.ChartObjects.Visible = True
        On Error Resume Next
        ChartObjects(Target.Value).Visible = True
End if
End Sub

(Make sure to change $A$2 to whatever cell you're using otherwise you might get an error, though it might not matter for this purpose).

The only difference between this code and the last is that I changed the "false" to "true". Essentially, this code just says to keep all charts visible at all times, no matter what the value of A2 is.

Keep the code this way until you've named all of your charts correctly. Once you're done naming them, change that true back to false.

KiaOrana · Jan 30, 2012

Thanks a lot 3link, it worked perfectly, I recovered all my work.

Unfortunately, I don't think I can use this code except if I can include another condition.

Let me explain. On my dashboard, I have 15 graphs that need to be displayed unconditionally, then I have a series of 7 graphs that needs to be displayed following the condition selected in my dropdown list let’s say in $A$2. These seven graphs are called “Monday” “Tuesday” “Wednesday “ and so on…

So I would like to display the 15 graphs plus let’s say “Monday” or “Tuesday” depending on what is in $A$2

IS that possible?

3link · Jan 30, 2012

KiaOrana said:
Thanks a lot 3link, it worked perfectly, I recovered all my work.

Unfortunately, I don't think I can use this code except if I can include another condition.

Let me explain. On my dashboard, I have 15 graphs that need to be displayed unconditionally, then I have a series of 7 graphs that needs to be displayed following the condition selected in my dropdown list let’s say in $A$2. These seven graphs are called “Monday” “Tuesday” “Wednesday “ and so on…

So I would like to display the 15 graphs plus let’s say “Monday” or “Tuesday” depending on what is in $A$2

IS that possible?

Probably. Give me a few hours to come up with something.

KiaOrana · Jan 30, 2012

Thanks a lot, your help is much appreciated

Conditionally show/hide charts

3link

Board Regular

Excel Facts

KiaOrana

New Member

3link

Board Regular

KiaOrana

New Member

KiaOrana

New Member

KiaOrana

New Member

3link

Board Regular

KiaOrana

New Member

3link

Board Regular

KiaOrana

New Member

Similar threads

Forum statistics

Share this page

Conditionally show/hide charts

Board Regular

Excel Facts

New Member

Board Regular

New Member

New Member

New Member

Board Regular

New Member

Board Regular

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock