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