How to only display charts I want to display?

[RaviWildcat]

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

 

[Taul]

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

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

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.

 

[RaviWildcat]

Thank you Taul!! I appreciate it!

 

[Jon Peltier]

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.