Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Workbook_Open() Event | Excel Discussion (Misc queries) | |||
Workbook_open Event | Excel Programming | |||
Workbook_Open Event | Excel Programming | |||
WorkBook_Open Event | Excel Programming | |||
OnTime event not firing in Workbook_Open event procedure | Excel Programming |