Enable outlining in a protected worksheet

[johnb]

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]

By naming the subroutine Autpen 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]

Thanks Dave, this solved my problem! I have another question please.
Autpen 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: Autpen," due to the same name being used in
different general modules within the same workbook. Is there a way to use
Autpen in several worksheets in the same workbook? Thanks.

 

[johnb]

Thanks Dave, this solved my problem! I have another question please.
Autpen 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: Autpen," due to the same name "Autpen" being
used in different general modules within the same workbook. If I
differentiate the name however, it will not recognize the Autpen command
and then I have to run the macro manually. Is there a way around this?
Thanks.

 

[Dave Peterson]

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]

Thanks Dave, this works great! I have another question please. How if
possible, can I circumvent the problem of an "ambiguous name detected:
Autpen," error message that results from using this procedure in several
worksheets of the same workbook? Thanks

 

[Dave Peterson]

This is not code that should be used in an any worksheet module.

Did you read my earlier response?

 

[johnb]

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]

Thanks for the explanation.

Glad you got it working.

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!