Macro Run-Time error - Items (charts) not found

[Rusty315]

Hi

I've got an interactive chart on a sheet where users select a chart from a combo box control and a picture link to the chart is displayed. The actual charts are on a hidden sheet. I have to activate the charts first for it to work so I put a button the sheet and recorded a macro where I unhide the hidden sheet, select each chart as I scroll down the screen by clicking on it then hide the sheet again and return to the interactive chart.

My problem is that I'm getting an error when the macro tries to select the first chart which is called "Chart 6":

Run-time error '-2147024809 (80070057)':

The item with the specified name wasn't found

I recorded the macro so I'm not sure why the macro isn't working now.

Sub ResetCharts()
'
' ResetCharts Macro
' Reset all charts
'

'
    Sheets("Budget v Actual Graphs").Select
    Sheets("Graph BG").Visible = True
    ActiveSheet.ChartObjects("Chart 6").Activate
    ActiveChart.ChartArea.Select
    ActiveWindow.SmallScroll Down:=15
    ActiveSheet.ChartObjects("Chart 35").Activate
    ActiveWindow.SmallScroll Down:=21
    ActiveSheet.ChartObjects("Chart 36").Activate
    ActiveWindow.SmallScroll Down:=42
    ActiveSheet.ChartObjects("Chart 38").Activate
    ActiveWindow.SmallScroll Down:=24
    ActiveSheet.ChartObjects("Chart 39").Activate
    ActiveWindow.SmallScroll Down:=18
    ActiveSheet.ChartObjects("Chart 40").Activate
    ActiveWindow.SmallScroll Down:=21
    ActiveSheet.ChartObjects("Chart 43").Activate
    ActiveWindow.SmallScroll Down:=21
    ActiveSheet.ChartObjects("Chart 41").Activate
    ActiveWindow.SmallScroll Down:=24
    ActiveSheet.ChartObjects("Chart 42").Activate
    ActiveWindow.SmallScroll Down:=21
    ActiveSheet.ChartObjects("Chart 44").Activate
    ActiveWindow.SmallScroll Down:=21
    ActiveSheet.ChartObjects("Chart 45").Activate
    ActiveWindow.SmallScroll Down:=24
    ActiveSheet.ChartObjects("Chart 46").Activate
    ActiveWindow.SmallScroll Down:=18
    ActiveSheet.ChartObjects("Chart 47").Activate
    ActiveWindow.SmallScroll Down:=21
    ActiveSheet.ChartObjects("Chart 48").Activate
    ActiveWindow.SmallScroll Down:=21
    ActiveSheet.ChartObjects("Chart 49").Activate
    ActiveWindow.SmallScroll Down:=24
    ActiveSheet.ChartObjects("Chart 50").Activate
    ActiveWindow.SmallScroll Down:=21
    ActiveSheet.ChartObjects("Chart 51").Activate
    ActiveWindow.SmallScroll Down:=21
    ActiveSheet.ChartObjects("Chart 52").Activate
    ActiveWindow.SmallScroll Down:=21
    ActiveSheet.ChartObjects("Chart 53").Activate
    ActiveWindow.SmallScroll Down:=24
    ActiveSheet.ChartObjects("Chart 54").Activate
    ActiveChart.Axes(xlValue).MajorGridlines.Select
    ActiveWindow.SmallScroll Down:=18
    ActiveSheet.ChartObjects("Chart 55").Activate
    ActiveWindow.SmallScroll Down:=21
    ActiveSheet.ChartObjects("Chart 56").Activate
    ActiveWindow.SmallScroll Down:=21
    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
    ActiveChart.Axes(xlValue).MajorGridlines.Select
    ActiveWindow.SmallScroll Down:=18
    ActiveSheet.ChartObjects("Chart 61").Activate
    ActiveWindow.SmallScroll Down:=24
    ActiveSheet.ChartObjects("Chart 62").Activate
    ActiveWindow.SmallScroll Down:=21
    ActiveSheet.ChartObjects("Chart 63").Activate
    ActiveWindow.SmallScroll Down:=30
    ActiveSheet.ChartObjects("Chart 32").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveWindow.SmallScroll Down:=24
    ActiveSheet.ChartObjects("Chart 33").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveWindow.SmallScroll Down:=24
    ActiveSheet.ChartObjects("Chart 34").Activate
    ActiveWindow.SmallScroll Down:=15
    Range("A711").Select
    Selection.End(xlUp).Select
    Sheets("Graph BG").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Budget v Actual Graphs").Select
End Sub

I'm only just learning how to use VB so I'm at a loss. Any help anyone can give me would be fantastic.

Cheers!

 

[Teeroy]

Try activating the "Graph BG" sheet after you make it visible.

Sheets("Graph BG").Activate

 

[Rusty315]

Works perfectly now! Thanks Teeroy. Now to figure out a way to speed it up somewhat. I'll try getting rid of the scrolling, not sure I need to do that.

Really appreciate your help, thank you!

 

[Teeroy]

You're welcome. If you need to activate every chart on the sheet, but don't want to make it visible, try the following (untested):

Sub Test()
Application.ScreenUpdating = False
With Sheets("Graph BG")
    For Each oChart In .ChartObjects
        oChart.Activate
    Next
End With
Application.ScreenUpdating = True
End Sub

 

[Rusty315]

That's awesome, thank you very much! I feel bad now as it appears I've wasted your time. The interactive chart is in a shared workbook and once I finished editing it and shared it, the macro stopped working. I get a message saying 'Run-time error '-2147024809 (80070057)': Requested shapes are locked for selection. Took about five minute of research to learn that most (all?) macros don't work in shared workbooks. Of course, I only learned this after I finally got it working.

Lucky I shave my head or I would've been pulling my hair out.

Thanks for your help anyway, Teeroy. It's genuinely appreciated!

 

[Teeroy]

Glad to help, pity you can't use it with the way you've got the workbook shared. From what I've been told, sharing a workbook is like starting a time bomb - it's a matter of when the file goes into meltdown, not if. After I heard that I steer clear of them.