Running macro on hidden sheet

[Rusty315]

Hi everyone

Before I start, I'm very, very new to VBA so this might be a stupid question and I probably haven't done this in a very elegant way.

Using Excel 2007, I created an interactive chart that uses picture links to charts on a hidden sheet but for some reason beyond my understanding, the interactive chart only works when I actually select the hidden charts first by clicking on them.

I've recorded a macro which, upon opening the workbook, selects each of the charts. This gets the interactive chart working but the problem is that when you open the workbook, you see the hidden sheet become unhidden, charts flick up and down the screen then the sheet is hidden again:

Private Sub Workbook_Open()
'
' SelectCharts Macro
'

'
Sheets("GRAPHS").Select
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.PlotArea.Select
ActiveWindow.SmallScroll Down:=180
Range("M17,D195").Select
Range("D195").Activate
ActiveSheet.ChartObjects("Chart 42").Activate
ActiveChart.ChartArea.Select
ActiveSheet.ChartObjects("Chart 42").Activate
ActiveChart.ChartArea.Select
ActiveWindow.SmallScroll Down:=18
ActiveSheet.ChartObjects("Chart 44").Activate
ActiveWindow.SmallScroll Down:=27
ActiveSheet.ChartObjects("Chart 45").Activate
ActiveWindow.SmallScroll Down:=27
ActiveSheet.ChartObjects("Chart 46").Activate
ActiveWindow.SmallScroll Down:=15
ActiveSheet.ChartObjects("Chart 47").Activate
ActiveWindow.SmallScroll Down:=24
ActiveSheet.ChartObjects("Chart 48").Activate
ActiveWindow.SmallScroll Down:=18
ActiveSheet.ChartObjects("Chart 49").Activate
ActiveWindow.SmallScroll Down:=24
ActiveSheet.ChartObjects("Chart 50").Activate
ActiveWindow.SmallScroll Down:=24
ActiveSheet.ChartObjects("Chart 51").Activate
ActiveWindow.SmallScroll Down:=15
ActiveSheet.ChartObjects("Chart 52").Activate
ActiveWindow.SmallScroll Down:=21
ActiveSheet.ChartObjects("Chart 53").Activate
ActiveWindow.SmallScroll Down:=21
ActiveSheet.ChartObjects("Chart 54").Activate
ActiveWindow.SmallScroll Down:=21
ActiveSheet.ChartObjects("Chart 55").Activate
ActiveWindow.SmallScroll Down:=15
ActiveSheet.ChartObjects("Chart 56").Activate
ActiveWindow.SmallScroll Down:=27
ActiveSheet.ChartObjects("Chart 57").Activate
ActiveWindow.SmallScroll Down:=18
ActiveSheet.ChartObjects("Chart 58").Activate
ActiveWindow.SmallScroll Down:=21
ActiveSheet.ChartObjects("Chart 59").Activate
ActiveWindow.SmallScroll Down:=24
ActiveSheet.ChartObjects("Chart 60").Activate
ActiveWindow.SmallScroll Down:=15
ActiveSheet.ChartObjects("Chart 61").Activate
ActiveWindow.SmallScroll Down:=30
ActiveSheet.ChartObjects("Chart 62").Activate
ActiveWindow.SmallScroll Down:=15
ActiveSheet.ChartObjects("Chart 63").Activate
ActiveWindow.SmallScroll Down:=15
Range("O625").Select
Sheets("GRAPHS").Select
ActiveWindow.SelectedSheets.Visible = False
End Sub

While it does the job and works fine, the report is used by my managing director and it just doesn't look pretty or professional when this normally hidden sheet suddenly become unhidden and a bunch of charts start flicking up the screen before being hidden again.

Is there a way to run this macro without having the hidden sheet appear?

Appreciate any help anyone can give me!

Cheers

 

[Andrew Poulsom]

What cause the charts to be interactive? You can use:

Application.ScreenUpdating = False

to suppress any screen refreshes.

 

[Rusty315]

Hi Andrew

Thanks for your help.

The chart is interactive because users can choose the chart they want to see from a list in a combo box control:

How to create an Interactive Chart in Excel? [Tutorial] | Chandoo.org - Learn Microsoft Excel Online

The actual charts are all on a hidden page. The combo box control actually only shows you a picture link, not the actual chart. It's a very handy trick, great for providing a lot of info in a small space but there are a few bugs in Excel 2007 that require the charts to be refreshed. I just fixed it a moment ago without the macro by making the zoom on the hidden page 10% which makes all the charts are visible when you view that page. The interactive chart works fine now.