How to only display charts I want to display?

RaviWildcat

Board Regular
Joined
Jun 18, 2010
Messages
97
Hi everyone, I've got a question about charts:

Background:

There are 5 cities I am tracking. I've created a separate graph for each one. Atlantic City, Bayonne, Hoboken, Jersey City, and Newark

There are 3 users, Greg, Marcia, and Mike

Greg can see data for Atlantic City, Bayonne and Hoboken

Marcia can see data for Jersey City and Newark

Mike can see data for all 5 cities.


So - I'm creating a page where -

At the top of the screen select a user (Greg, Marcia, Mike)

Show the graphs for the cities associated with each user.

I've figured out how to display the data based on which user I select (I'm using a series of helper columns and indexes)

However!

If a user selects Greg, I only want to see 3 charts. If a user selects Marcia, I only want to see 2 charts and if a user selects Mike I want to see all 5 charts

Is this possible?

Thanks,
Ravi
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Taul

Well-known Member
Joined
Oct 24, 2004
Messages
752
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
Hi Ravi,
Yes, this is possible but you will need to use VBA.
If you click on one of your graphs and look in the named range box just above and to the left of the worksheet, you will see the name of the chart, I just created 5 charts, they are called Chart 1, Chart 2 etc.
Each chart has "Properties", one of the properties is "Visibility" and we can turn the visibility on and off by making the value "True" or "False"
Typically:-
Code:
ActiveSheet.ChartObjects("Chart 1").Visible = True

TRUE means we can see it
FALSE means we can not see it

When you select a user at the top of your sheet, I assume you are using a Data Validation drop down box, with the names Greg, Marcia & Mike. Is this correct?
Assuming this is correct, I have set up a sheet with 5 graphs.

The following VBA code will need to go into the sheet module
Hold your cursor over the page tab, right click and select “View Code”
When the VBE editor opens, the list of pages will be on the eft and provided your sheet is selected, click once in the right hand pane and paste the VBA code.

Adjust the names of the charts to the actual names you have on your charts.
Adjust the cell ref to whichever cell houses your data validation dropdown list of names, my sample page uses cell E2

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Application.ScreenUpdating = False
'Application.Calculation = xlCalculationManual

'Limit the target range to one cell, the drop down menu cell E2
Dim rng1 As Range
If Target.Count > 1 Then Exit Sub

Set rng1 = Range("E2")
If Intersect(Target, rng1) Is Nothing Then Exit Sub

'Display/Switch the charts on and off - use Select Case " method"
Dim WhosStuff As String
WhosStuff = Range("E2").Value 'WhosStuff is the name in cell E2

Select Case WhosStuff

Case Is = "Greg"
ActiveSheet.ChartObjects("Chart 1").Visible = True
ActiveSheet.ChartObjects("Chart 2").Visible = True
ActiveSheet.ChartObjects("Chart 3").Visible = True
ActiveSheet.ChartObjects("Chart 4").Visible = False
ActiveSheet.ChartObjects("Chart 5").Visible = False

Case Is = "Marcia"
ActiveSheet.ChartObjects("Chart 1").Visible = False
ActiveSheet.ChartObjects("Chart 2").Visible = False
ActiveSheet.ChartObjects("Chart 3").Visible = False
ActiveSheet.ChartObjects("Chart 4").Visible = True
ActiveSheet.ChartObjects("Chart 5").Visible = True

Case Is = "Mike"
ActiveSheet.ChartObjects("Chart 1").Visible = True
ActiveSheet.ChartObjects("Chart 2").Visible = True
ActiveSheet.ChartObjects("Chart 3").Visible = True
ActiveSheet.ChartObjects("Chart 4").Visible = True
ActiveSheet.ChartObjects("Chart 5").Visible = True

End Select

'Application.ScreenUpdating = True
'Application.Calculation = xlCalculationAutomatic

End Sub

Hope this helps
Paul.
 

Attachments

  • pic1.jpg
    pic1.jpg
    243.2 KB · Views: 6

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,962
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
When the chart name is showing in the name box, change it from Chart 1, Chart 2, etc., to something meaningful, like the city name. This makes it easier when you're writing the VBA code, so you know who can see which cities.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,500
Messages
5,636,690
Members
416,935
Latest member
Atulcp

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top