HELP to apply a Workbook_Open event

[Metallo]

Hi,

I've been given in this forum the following code to get one protected sheet
the possibility to be outlined.
Of course, if I add more sheets in the code it should work as well.

I use XP and Office 2003
I open "my workbook", then, ALT+F11, select into the VBAProject the sheet I
want to apply the event, double click and copy the following:

Private Sub Workbook_Open()
'''Enable Outlining navigation and protect everything on the sheet with
UserInterfaceOnly.
Sheet1.EnableOutlining = True
Sheet1.Protect , True, True, True, True
End Sub

Now, can anybody tell me how to continue in order to apply the event to the
sheet?
I'm sure this is a stupid question but cannot get it to work.

Thank you
Alex

 

[Ron de Bruin]

Hi Metallo

This event must be in the Thisworkbook module and not in a sheet module.
You use the code name of the sheet in your code instead of the tab name.(no problem)

This example use the Tab name, change it to the sheet you want and copy it in the
Private Sub Workbook_Open()

With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
.EnableOutlining = True
End With

Read this
Chip Pearson's site about Events
http://www.cpearson.com/excel/events.htm

 

[Metallo]

Ron,

I don't know why, but I get this error when I open the workbook:

Subscript out of range

Here the event:

Private Sub Workbook_Open()



With Worksheets("Sheet12")

UserInterfaceOnly = True

EnableOutlining = True

End With

End Sub

When I click debug, the "With Worksheets("Sheet12")" highlights in yellow.

Sheet12 is the code and not the tab, although I have tried with the tab and
get the same error.

Thanks
Alex

 

[Metallo]

Ron,

I solved the issue, by applying the original code I gave you, once placed
into the workbook, it worked fine.

But now, another issue came out.
In the workbook I have macros, something simple that change the formatting
to some of the sheets, but since the open event enables sheet protection,
obviously the macros do not work.

I can I overcome this problem?

Do I need to add some text to the event in order to allow formatting?

I work with Office 2003, but the workbooks are destined to users running
Office 2000.

Thanks
Alex

 

[Ron de Bruin]

Hi Metallo

You only protect the Sheet with codename Sheet1 in the code
Have you protect your other sheets manual?

 

[Metallo]

Ron,

No, I did apply the change to other sheets in the event, that was the
intention.
They are the same sheets that need formatting with the macro...that's why
the issue.

Alex

 

[Metallo]

Hi,

Can anybody help?

Thanks
Alex

Ron,

No, I did apply the change to other sheets in the event, that was the
intention.
They are the same sheets that need formatting with the macro...that's why
the issue.

Alex