ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   HELP to apply a Workbook_Open event (https://www.excelbanter.com/excel-programming/302626-help-apply-workbook_open-event.html)

Metallo[_4_]
HELP to apply a Workbook_Open event
 
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
HELP to apply a Workbook_Open event
 
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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Metallo" wrote in message ...
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

Metallo[_4_]
HELP to apply a Workbook_Open event
 
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

"Ron de Bruin" wrote in message
...
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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Metallo" wrote in message
...
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

Metallo[_4_]
HELP to apply a Workbook_Open event
 
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

"Metallo" wrote in message
...
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

"Ron de Bruin" wrote in message
...
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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Metallo" wrote in message
...
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
HELP to apply a Workbook_Open event
 
Hi Metallo

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Metallo" wrote in message ...
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

"Metallo" wrote in message
...
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

"Ron de Bruin" wrote in message
...
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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Metallo" wrote in message
...
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

Metallo[_4_]
HELP to apply a Workbook_Open event
 
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

"Ron de Bruin" wrote in message
...
Hi Metallo

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Metallo" wrote in message
...
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

"Metallo" wrote in message
...
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

"Ron de Bruin" wrote in message
...
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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Metallo" wrote in message
...
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

Metallo[_4_]
HELP to apply a Workbook_Open event
 
Hi,

Can anybody help?

Thanks
Alex

"Metallo" wrote in message
...
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

"Ron de Bruin" wrote in message
...
Hi Metallo

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Metallo" wrote in message
...
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

"Metallo" wrote in message
...
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

"Ron de Bruin" wrote in message
...
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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Metallo" wrote in message
...
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


All times are GMT +1. The time now is 08:49 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com