It's VBA, not VBS.

And did you put that code in a general module?
Did you change the name of the sheet to match what you need?
Did you enable macros when the workbook opened?



Chuck D wrote:

Hi Dave,

I too tried to key in this macro (assuming it's supposed to be in VBS?) but
it does not work when I re-launch the spreadsheet. Any help is appreciated.

Thanks, Chuck dressler

"Dave Peterson" wrote:

Change the sheet name to match your situation--and the password, too.

And put that code into a General module.

And then close/save the workbook and reopen it -- make sure macros are enabled.

John M wrote:

Rich, I'm having the same exact issue you were having with wanting to
protect a worksheet, but also give users the option to expand a collapse
groups. I tried to enter the code Dave Peterson recommended, but it doesn't
seem to work. Did you enter it in exaclty as he has written it? Thanks

"Rich K" wrote:

Thank you. This seems to solve it.

"Dave Peterson" wrote:

If you already have the outline/subtotals applied, you can protect the worksheet
in code (auto_open/workbook_open??).

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
.EnableOutlining = True
.EnableAutoFilter = True
End With
End Sub

It needs to be reset each time you open the workbook. (excel doesn't remember
it after closing the workbook.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Rich K wrote:

In order make the UI a little cleaner on a spreadsheet with a lot of data, I
have added grouped columns and rows for the user to easily collapse the data
and re-expand. This sheet is also needs to be protected from data entry
except in the few cells that are unlocked.

If I turn on the sheet protection and click on a group to expand or
collapse, I get the error "You cannot us this command on a protected
sheet......" Does anyone know how I may be able to trap this event in VBA
and then write some code to determine the type of command that was tried?
If I could determine this, then I could selectively unprotect the sheet and
group or ungroup and then protect the sheet and return control to the user.

Thanks.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson