Format Icon

[Doug]

I have a workbook with 5 sheets and 5 userforms. The user is supposed to
have access to 1 form. All the sheets are locked or unlocked as required to
perform various proceedures. Only the administrator should have access to
the various sheets and input forms. This access is password protected. The
code I used to lock everything was:

ActiveSheet.Protect Password:=TheWord, Contents:=True, Scenarios:=True, _
DrawingObjects:=True
ActiveSheet.Unprotect Password:=TheWord

The problem is, the Format Icon on the Excel toolbar is still active which
gives the user access to 'unhide sheets' This exposes the other sheets
which are locked, but the command buttons are now visible and active which
gives the user full access to everything.

Is there code I can add to mine so I can deactivate the Format icon to limit
user acess?

Thanks for the help.
Doug

 

[Rick Rothstein]

You are protecting the active sheet, not *all* sheets... the protection only
applies to that one sheet. What you need to do is protect the workbook
structure as well. Try something like this...

ThisWorkbook.Protect Password:=TheWord, Structure:=True

That should stop the user from being able to hide/unhide (or even move) the
worksheets.

 

[Doug]

Protecting the Workbook limits the Format button, but now the sheet is so
locked up, non of my other proceedures work. It looks like I will have to
unlock the workbook for each proceedure, then relock it at the end of each
Sub. Is there an easier way to do this? Can I just disable the Format icon?

Doug