Running macro on hidden sheet

Rusty315

Board Regular
Joined
Sep 13, 2012
Messages
54
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
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
What cause the charts to be interactive? You can use:

Application.ScreenUpdating = False

to suppress any screen refreshes.
 

Rusty315

Board Regular
Joined
Sep 13, 2012
Messages
54
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,915
Messages
5,598,850
Members
414,263
Latest member
sherrcha

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