Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Enable outlining in a protected worksheet
I currently have the following code in my protected worksheet, which enables
outlining. I have also created a button titled 'outline' to run the macro so the user does not have to go to the tools/macros/run. Option Explicit Sub auto_open() With Worksheets("sheet1") .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True End With End Sub My worry however is that once the macro is run and outlining is enabled, some novice users will still find it tedious to click on every '+' and "-" to show/hide all the detail, especially if there are a lot of groupings. Is there code on a protected worksheet that will show/hide all the detail at once, equivalent to selecting ctrl A and then Data/Group and outline/show & hide detail on an unprotected sheet? Thank you. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Enable outlining in a protected worksheet
By naming the subroutine Auto_Open and putting it in a General module (not
behind the ThisWorkbook module and not behind a worksheet module), the routine should run whenever the user opens the workbook (assuming that they allow macros to run at startup). And take a look at the left hand margin above those +'s and -'s. You'll see numbers in little boxes. Try clicking on them and watch what happens to the hidden/shown rows. johnb wrote: I currently have the following code in my protected worksheet, which enables outlining. I have also created a button titled 'outline' to run the macro so the user does not have to go to the tools/macros/run. Option Explicit Sub auto_open() With Worksheets("sheet1") .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True End With End Sub My worry however is that once the macro is run and outlining is enabled, some novice users will still find it tedious to click on every '+' and "-" to show/hide all the detail, especially if there are a lot of groupings. Is there code on a protected worksheet that will show/hide all the detail at once, equivalent to selecting ctrl A and then Data/Group and outline/show & hide detail on an unprotected sheet? Thank you. -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Enable outlining in a protected worksheet
Thanks Dave, this solved my problem! I have another question please.
Auto_Open works great in one worksheet of a workbook, however if I am using this in multiuple worksheets in a single workbook, I get the error message "Ambiguous name detected: Auto_Open," due to the same name being used in different general modules within the same workbook. Is there a way to use Auto_Open in several worksheets in the same workbook? Thanks. "Dave Peterson" wrote: By naming the subroutine Auto_Open and putting it in a General module (not behind the ThisWorkbook module and not behind a worksheet module), the routine should run whenever the user opens the workbook (assuming that they allow macros to run at startup). And take a look at the left hand margin above those +'s and -'s. You'll see numbers in little boxes. Try clicking on them and watch what happens to the hidden/shown rows. johnb wrote: I currently have the following code in my protected worksheet, which enables outlining. I have also created a button titled 'outline' to run the macro so the user does not have to go to the tools/macros/run. Option Explicit Sub auto_open() With Worksheets("sheet1") .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True End With End Sub My worry however is that once the macro is run and outlining is enabled, some novice users will still find it tedious to click on every '+' and "-" to show/hide all the detail, especially if there are a lot of groupings. Is there code on a protected worksheet that will show/hide all the detail at once, equivalent to selecting ctrl A and then Data/Group and outline/show & hide detail on an unprotected sheet? Thank you. -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Enable outlining in a protected worksheet
Thanks Dave, this solved my problem! I have another question please.
Auto_Open works great in one worksheet of a workbook, however if I am using this in multiuple worksheets in a single workbook, I get the error message "Ambiguous name detected: Auto_Open," due to the same name "Auto_Open" being used in different general modules within the same workbook. If I differentiate the name however, it will not recognize the Auto_Open command and then I have to run the macro manually. Is there a way around this? Thanks. "Dave Peterson" wrote: By naming the subroutine Auto_Open and putting it in a General module (not behind the ThisWorkbook module and not behind a worksheet module), the routine should run whenever the user opens the workbook (assuming that they allow macros to run at startup). And take a look at the left hand margin above those +'s and -'s. You'll see numbers in little boxes. Try clicking on them and watch what happens to the hidden/shown rows. johnb wrote: I currently have the following code in my protected worksheet, which enables outlining. I have also created a button titled 'outline' to run the macro so the user does not have to go to the tools/macros/run. Option Explicit Sub auto_open() With Worksheets("sheet1") .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True End With End Sub My worry however is that once the macro is run and outlining is enabled, some novice users will still find it tedious to click on every '+' and "-" to show/hide all the detail, especially if there are a lot of groupings. Is there code on a protected worksheet that will show/hide all the detail at once, equivalent to selecting ctrl A and then Data/Group and outline/show & hide detail on an unprotected sheet? Thank you. -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Enable outlining in a protected worksheet
You have to combine the code into one larger routine.
If you wanted to do the same kind of thing to a couple of sheets: Option Explicit Sub auto_open() With Worksheets("sheet1") .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True End With With Worksheets("sheet2") .Protect Password:="nothi", userinterfaceonly:=True .EnableOutlining = True End With 'more code you want here End Sub If the passwords were the same, you could actually loop through the worksheets and not have so much almost duplicated code: Option Explicit Sub auto_open() Dim wks as worksheet for each wks in thisworkbook.worksheets with wks .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True End With next wks 'more code you want here End Sub johnb wrote: Thanks Dave, this solved my problem! I have another question please. Auto_Open works great in one worksheet of a workbook, however if I am using this in multiuple worksheets in a single workbook, I get the error message "Ambiguous name detected: Auto_Open," due to the same name "Auto_Open" being used in different general modules within the same workbook. If I differentiate the name however, it will not recognize the Auto_Open command and then I have to run the macro manually. Is there a way around this? Thanks. "Dave Peterson" wrote: By naming the subroutine Auto_Open and putting it in a General module (not behind the ThisWorkbook module and not behind a worksheet module), the routine should run whenever the user opens the workbook (assuming that they allow macros to run at startup). And take a look at the left hand margin above those +'s and -'s. You'll see numbers in little boxes. Try clicking on them and watch what happens to the hidden/shown rows. johnb wrote: I currently have the following code in my protected worksheet, which enables outlining. I have also created a button titled 'outline' to run the macro so the user does not have to go to the tools/macros/run. Option Explicit Sub auto_open() With Worksheets("sheet1") .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True End With End Sub My worry however is that once the macro is run and outlining is enabled, some novice users will still find it tedious to click on every '+' and "-" to show/hide all the detail, especially if there are a lot of groupings. Is there code on a protected worksheet that will show/hide all the detail at once, equivalent to selecting ctrl A and then Data/Group and outline/show & hide detail on an unprotected sheet? Thank you. -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Enable outlining in a protected worksheet
Thanks Dave, this works great! I have another question please. How if
possible, can I circumvent the problem of an "ambiguous name detected: Auto_Open," error message that results from using this procedure in several worksheets of the same workbook? Thanks "Dave Peterson" wrote: By naming the subroutine Auto_Open and putting it in a General module (not behind the ThisWorkbook module and not behind a worksheet module), the routine should run whenever the user opens the workbook (assuming that they allow macros to run at startup). And take a look at the left hand margin above those +'s and -'s. You'll see numbers in little boxes. Try clicking on them and watch what happens to the hidden/shown rows. johnb wrote: I currently have the following code in my protected worksheet, which enables outlining. I have also created a button titled 'outline' to run the macro so the user does not have to go to the tools/macros/run. Option Explicit Sub auto_open() With Worksheets("sheet1") .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True End With End Sub My worry however is that once the macro is run and outlining is enabled, some novice users will still find it tedious to click on every '+' and "-" to show/hide all the detail, especially if there are a lot of groupings. Is there code on a protected worksheet that will show/hide all the detail at once, equivalent to selecting ctrl A and then Data/Group and outline/show & hide detail on an unprotected sheet? Thank you. -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Enable outlining in a protected worksheet
This is not code that should be used in an any worksheet module.
Did you read my earlier response? johnb wrote: Thanks Dave, this works great! I have another question please. How if possible, can I circumvent the problem of an "ambiguous name detected: Auto_Open," error message that results from using this procedure in several worksheets of the same workbook? Thanks "Dave Peterson" wrote: By naming the subroutine Auto_Open and putting it in a General module (not behind the ThisWorkbook module and not behind a worksheet module), the routine should run whenever the user opens the workbook (assuming that they allow macros to run at startup). And take a look at the left hand margin above those +'s and -'s. You'll see numbers in little boxes. Try clicking on them and watch what happens to the hidden/shown rows. johnb wrote: I currently have the following code in my protected worksheet, which enables outlining. I have also created a button titled 'outline' to run the macro so the user does not have to go to the tools/macros/run. Option Explicit Sub auto_open() With Worksheets("sheet1") .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True End With End Sub My worry however is that once the macro is run and outlining is enabled, some novice users will still find it tedious to click on every '+' and "-" to show/hide all the detail, especially if there are a lot of groupings. Is there code on a protected worksheet that will show/hide all the detail at once, equivalent to selecting ctrl A and then Data/Group and outline/show & hide detail on an unprotected sheet? Thank you. -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Enable outlining in a protected worksheet
Dave,
Sorry about the multiple posts. My connection was down earlier and giving me error messages everytime I tried to reply to your original post. Apparently the my responses did go through. I combined everything into one larger outline and it works perfectly! Thank you very much! "Dave Peterson" wrote: This is not code that should be used in an any worksheet module. Did you read my earlier response? johnb wrote: Thanks Dave, this works great! I have another question please. How if possible, can I circumvent the problem of an "ambiguous name detected: Auto_Open," error message that results from using this procedure in several worksheets of the same workbook? Thanks "Dave Peterson" wrote: By naming the subroutine Auto_Open and putting it in a General module (not behind the ThisWorkbook module and not behind a worksheet module), the routine should run whenever the user opens the workbook (assuming that they allow macros to run at startup). And take a look at the left hand margin above those +'s and -'s. You'll see numbers in little boxes. Try clicking on them and watch what happens to the hidden/shown rows. johnb wrote: I currently have the following code in my protected worksheet, which enables outlining. I have also created a button titled 'outline' to run the macro so the user does not have to go to the tools/macros/run. Option Explicit Sub auto_open() With Worksheets("sheet1") .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True End With End Sub My worry however is that once the macro is run and outlining is enabled, some novice users will still find it tedious to click on every '+' and "-" to show/hide all the detail, especially if there are a lot of groupings. Is there code on a protected worksheet that will show/hide all the detail at once, equivalent to selecting ctrl A and then Data/Group and outline/show & hide detail on an unprotected sheet? Thank you. -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Enable outlining in a protected worksheet
Thanks for the explanation.
Glad you got it working. johnb wrote: Dave, Sorry about the multiple posts. My connection was down earlier and giving me error messages everytime I tried to reply to your original post. Apparently the my responses did go through. I combined everything into one larger outline and it works perfectly! Thank you very much! "Dave Peterson" wrote: This is not code that should be used in an any worksheet module. Did you read my earlier response? johnb wrote: Thanks Dave, this works great! I have another question please. How if possible, can I circumvent the problem of an "ambiguous name detected: Auto_Open," error message that results from using this procedure in several worksheets of the same workbook? Thanks "Dave Peterson" wrote: By naming the subroutine Auto_Open and putting it in a General module (not behind the ThisWorkbook module and not behind a worksheet module), the routine should run whenever the user opens the workbook (assuming that they allow macros to run at startup). And take a look at the left hand margin above those +'s and -'s. You'll see numbers in little boxes. Try clicking on them and watch what happens to the hidden/shown rows. johnb wrote: I currently have the following code in my protected worksheet, which enables outlining. I have also created a button titled 'outline' to run the macro so the user does not have to go to the tools/macros/run. Option Explicit Sub auto_open() With Worksheets("sheet1") .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True End With End Sub My worry however is that once the macro is run and outlining is enabled, some novice users will still find it tedious to click on every '+' and "-" to show/hide all the detail, especially if there are a lot of groupings. Is there code on a protected worksheet that will show/hide all the detail at once, equivalent to selecting ctrl A and then Data/Group and outline/show & hide detail on an unprotected sheet? Thank you. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I enable auto-expanding lists in a protected worksheet? | Excel Discussion (Misc queries) | |||
Outlining in Protected Sheet - Attention DP | Excel Discussion (Misc queries) | |||
enable commentaries when is protected | Excel Worksheet Functions | |||
enable autofilter in a protected worksheet in Excel 97 | Excel Worksheet Functions | |||
How to enable font color on protected worksheet? | Excel Discussion (Misc queries) |