Using Collapse/Expand with Protected worksheets

Hello -- someone has posted this great code that works well to allow
this, but you need to list each individual worksheet. The problem is,
i want to give my users the option of changing the worksheet tab title,
and in fact this title is referenced repeatedly throughout the
workbook.

isn't there a way to apply this macro to "all worksheets" regardless of
the name?

the original code is below. Thanks very much!:

Private Sub Workbook_Open()


Dim mySheetNames As Variant
Dim iCtr As Long


'change to match your workbook
mySheetNames = Array("sheet1", "sheet2", "sheet3")


For iCtr = LBound(mySheetNames) To UBound(mySheetNames)
With Worksheets(mySheetNames(iCtr))
.Select
.EnableOutlining = True
.Protect Password:="password", _
Contents:=True, UserInterfaceOnly:=True
End With
Next iCtr
End Sub

Option explicit
Private Sub Workbook_Open()

dim Wks as worksheet

for each wks in me.worksheets
With wks
.Select
.EnableOutlining = True
.Protect Password:="password", _
Contents:=True, UserInterfaceOnly:=True
End With
Next wks

End Sub

is one way.


stuck wrote:
>
> Hello -- someone has posted this great code that works well to allow
> this, but you need to list each individual worksheet. The problem is,
> i want to give my users the option of changing the worksheet tab title,
> and in fact this title is referenced repeatedly throughout the
> workbook.
>
> isn't there a way to apply this macro to "all worksheets" regardless of
> the name?
>
> the original code is below. Thanks very much!:
>
> Private Sub Workbook_Open()
>
> Dim mySheetNames As Variant
> Dim iCtr As Long
>
> 'change to match your workbook
> mySheetNames = Array("sheet1", "sheet2", "sheet3")
>
> For iCtr = LBound(mySheetNames) To UBound(mySheetNames)
> With Worksheets(mySheetNames(iCtr))
> .Select
> .EnableOutlining = True
> .Protect Password:="password", _
> Contents:=True, UserInterfaceOnly:=True
> End With
> Next iCtr
> End Sub

--

Dave Peterson

Thanks for that but i'm getting a syntax error at the 2nd line. i know
nothing about VB so i am just plugging this in and hoping it works. any
help wld be great.
thx

Make sure you put the code under ThisWorkbook (and delete it from that General
module).

stuck wrote:
>
> Thanks for that but i'm getting a syntax error at the 2nd line. i know
> nothing about VB so i am just plugging this in and hoping it works. any
> help wld be great.
> thx

--

Dave Peterson

that's where it is. I put the code in the "ThisWorkbook" window (that
is now the only code in it), save, close, and reopen the excel file --
then i get a "compile error: syntax error" and the "Private Sub
Workbook_Open()" line is highlighted in yellow.

also the stray "." is highlighted in blue. is that "." correct? I
would greatly appreciate it if you could test this -- i tried it on a
blank worksheet and got the same error.

i am in urgent need as my whole org is waiting for this file.
thanks!

I think you've been hit by a google problem.

Google is inserting extra characters when you copy|paste from google.

I connect directly to the MSNewsservers and don't have to mess around with this.

I think your choices are to type the code in manually (or clean up all those
extra characters). (You may want to send a message to google asking them to fix
this. Maybe if enough people do it, it'll hit critical mass and they'll do
something!)

Or you could connect directly to the MSNewservers.

Saved from a previous post:

If you have Outlook Express installed, try clicking on these links (or copy and
paste into MSIE).

news://msnews.microsoft.com/microsof...ic.excel.setup
news://msnews.microsoft.com/microsoft.public.excel.misc
news://msnews.microsoft.com/microsof...heet.functions
news://msnews.microsoft.com/microsof...excel.newusers
news://msnews.microsoft.com/microsof...el.programming

(and a few more for MSWord)
news://msnews.microsoft.com/microsof....docmanagement
news://msnews.microsoft.com/microsof...word.word97vba
news://msnews.microsoft.com/microsof....word.newusers
news://msnews.microsoft.com/microsof...ord.pagelayout
news://msnews.microsoft.com/microsof...ord.vba.addins
news://msnews.microsoft.com/microsof....vba.beginners
news://msnews.microsoft.com/microsof....customization
news://msnews.microsoft.com/microsof...rd.vba.general
news://msnews.microsoft.com/microsof....vba.userforms
news://msnews.microsoft.com/microsof....word6-7macros

(You can always connect to more later)

Here are some links that explain it better:

Chip Pearson has some notes written by Leonard Meads at:
http://www.cpearson.com/excel/DirectConnect.htm

David McRitchie's notes at:
http://www.mvps.org/dmcritchie/excel/xlnews.htm
http://www.mvps.org/dmcritchie/excel/oe6.htm
http://www.mvps.org/dmcritchie/excel/oe6nws01.htm

Tushar Mehta's notes at:
http://www.tushar-mehta.com/misc_tut...e_ng/index.htm

And if you're looking for old posts:

Or you can use google (maybe a few hours behind) to search for stuff you've
posted (and find the replies, too)

http://groups.google.com/advanced_group_search
http://groups.google.com/advanced_gr...Excel*&num=100

Ron de Bruin has an excel addin that you may like:
http://www.rondebruin.nl/Google.htm

stuck wrote:
>
> that's where it is. I put the code in the "ThisWorkbook" window (that
> is now the only code in it), save, close, and reopen the excel file --
> then i get a "compile error: syntax error" and the "Private Sub
> Workbook_Open()" line is highlighted in yellow.
>
> also the stray "." is highlighted in blue. is that "." correct? I
> would greatly appreciate it if you could test this -- i tried it on a
> blank worksheet and got the same error.
>
> i am in urgent need as my whole org is waiting for this file.
> thanks!

--

Dave Peterson

Thanks for the quick reply-- i cleaned up the code and it works!

i appreciate the help.