hiding specific shapes on workbook open

[dirtywizard]

Hi all

I've set up a series of button click macros to guide users around an excel file I've built. These are linked to a series of 'storyboard' comments on the data they're looking at.

The current process takes users from sheet to sheet, hiding the commentary on the sheet moved from at the point the user moves to the next sheet (so that users can either choose to ignore the storyboard from the off, or go back without it in their way later on)

To fully close the loop I wanted to fire off a workbook_open macro to ensure all commentary is not visible when opening the workbook. I've written this:

[COLOR=#212121][FONT=Arial]Private Sub workbook_open()[/FONT][/COLOR][COLOR=#212121][FONT=Calibri][FONT=Arial]Application.ScreenUpdating = False[/FONT][/FONT][/COLOR]
[COLOR=#212121][FONT=Calibri][FONT=Arial]On Error Resume Next[/FONT][/FONT][/COLOR]
[COLOR=#212121][FONT=Calibri][FONT=Arial]Dim sp As Shape[/FONT][/FONT][/COLOR]
[COLOR=#212121][FONT=Calibri][FONT=Arial]Dim ws As Worksheet[/FONT][/FONT][/COLOR]
[COLOR=#212121][FONT=Calibri][FONT=Arial]For Each ws In ThisWorkbook.Sheets[/FONT][/FONT][/COLOR]
[COLOR=#212121][FONT=Calibri][FONT=Arial]For Each sp In ws.Shapes[/FONT][/FONT][/COLOR]
[COLOR=#212121][FONT=Calibri][FONT=Arial]If sp.Name Like "story*" Then[/FONT][/FONT][/COLOR]
[COLOR=#212121][FONT=Calibri][FONT=Arial]ActiveSheet.Shapes(sp.Name).Visible = False[/FONT][/FONT][/COLOR]
[COLOR=#212121][FONT=Calibri][FONT=Arial]End If[/FONT][/FONT][/COLOR]
[COLOR=#212121][FONT=Calibri][FONT=Arial]Next sp[/FONT][/FONT][/COLOR]
[COLOR=#212121][FONT=Calibri][FONT=Arial]Next ws[/FONT][/FONT][/COLOR]
[COLOR=#212121][FONT=Calibri][FONT=Arial]Worksheets("Contents").Activate[/FONT][/FONT][/COLOR]
[COLOR=#212121][FONT=Calibri][FONT=Arial]Application.ScreenUpdating = True[/FONT][/FONT][/COLOR]
[COLOR=#212121][FONT=Arial]End Sub[/FONT][/COLOR]

which isn't hiding the shapes as required - it does hide shapes on the contents sheet in a couple of tests run, but doesn't move through the sheets hiding as required.

Any ideas what I next to do to fix it?

 

[JoeMo]

What type of shapes are you using? Have you verified that they all have names that start with "story"? What happens if you comment out the "On Error Resume Next" line , save, close and re-open the workbook?

 

[dirtywizard]

Hi JoeMo

Removing the "on error resume next" pops up an error stating it can't find the named shape, and then the rest of the macro doesn't fire.

All shapes to be hidden are rounded rectangles (I wanted them to be prettier than command buttons) and have the prefix "story". I have macros that take users through each relevant sheet (sheet1/sheet2 etc), hiding the buttons on sheet1 when moving to sheet2 and showing the buttons on sheet2 etc. all of the other macros function as expected.

I've tested the open macro by making the shapes visible in individual sheets and then saving/opening whilst on that sheet - with the "on error resume next" line used it does hide the shapes on the first 'active' sheet, but doesn't do the cycling between each sheet

 

[JoeMo]

Hi JoeMo

Removing the "on error resume next" pops up an error stating it can't find the named shape, and then the rest of the macro doesn't fire.

All shapes to be hidden are rounded rectangles (I wanted them to be prettier than command buttons) and have the prefix "story". I have macros that take users through each relevant sheet (sheet1/sheet2 etc), hiding the buttons on sheet1 when moving to sheet2 and showing the buttons on sheet2 etc. all of the other macros function as expected.

I've tested the open macro by making the shapes visible in individual sheets and then saving/opening whilst on that sheet - with the "on error resume next" line used it does hide the shapes on the first 'active' sheet, but doesn't do the cycling between each sheet

This works for me when I have 4 rounded rectangles named story1, story2, .... , story4 on three different sheets.

Private Sub workbook_open()
Application.ScreenUpdating = False
Dim sp As Shape
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
    For Each sp In ws.Shapes
        If sp.Name Like "story*" Then
            sp.Visible = False
        End If
    Next sp
Next ws
Worksheets("Contents").Activate
Application.ScreenUpdating = True
End Sub

The code goes into a Thisworkbook module.