Protect/Unprotecting Sheets with CmdButtons
 

My workbook has 14 worksheets (4 of them are data sheets and are hidde
to the user). Most of the sheets have command buttons to ope
userForms to capture data and run reports.

I want to protect all the sheets to prevent the user from messing wit
the formulas, etc. In all of the userform_Initialization events, I a
running a UnprotectSheets module and a ProtectSheets module o
UserForm_Terminate events.

The problem is that I'm getting 1004 Runtime errors when trying t
protect/unprotect certain sheets (does not happen on just one specifi
sheet). When I try to protect/unprotect manally on the sheet by goin
to Tools/Protection... the protect/unprotect option is greyed out. I
becomes available when I click somewhere in the worksheet and then
can proceed with the manual way or even the macro way. I think it ha
something to do with what's on focus in the sheet. I think the shee
loses the focus when the button is clicked. (If that makes sense)

So, I thought I could be clever by selecting or activating a cel
(range) in each worksheet before protecting/unprotecting, but I stil
get the runtime error. The sheets are password protected. I'
accessing the password via a constant field. But I know it's not
problem with the password.

Sub ProtectSheets()
Dim ws As Worksheet
For each ws in ActiveWorkbook.Worksheets
ws.Range("A1").Select
ws.Protect constPassword
Next ws
End Sub

The unprotectSheets looks the same other than ws.Unprotec
constPassword

Do you have any suggestions

--
NikkiBen
-----------------------------------------------------------------------
NikkiBenz's Profile: http://www.excelforum.com/member.php...fo&userid=3416
View this thread: http://www.excelforum.com/showthread.php?threadid=53928

Protect/Unprotecting Sheets with CmdButtons
 

If this is being run from a commandbutton, then

Set the takefocusonclick property of the commandbutton to false.

also note that you can only select on the activesheet, so activate the sheet
before trying to select a cell on it.

also, if this code is actually located in a sheet module, then you have
perhaps fortuitously stumbled into the requirement to qualify any offsheet
range with a sheet reference. You have used ws.Range("A1").Select - even if
ws is the activesheet (which you need to make it that), if you used

Range("A1").Select

it would have referred to the sheet that contained the code.

--

Regards,
Tom Ogilvy




--
Regards,
Tom Ogilvy


"NikkiBenz" wrote:


My workbook has 14 worksheets (4 of them are data sheets and are hidden
to the user). Most of the sheets have command buttons to open
userForms to capture data and run reports.

I want to protect all the sheets to prevent the user from messing with
the formulas, etc. In all of the userform_Initialization events, I am
running a UnprotectSheets module and a ProtectSheets module on
UserForm_Terminate events.

The problem is that I'm getting 1004 Runtime errors when trying to
protect/unprotect certain sheets (does not happen on just one specific
sheet). When I try to protect/unprotect manally on the sheet by going
to Tools/Protection... the protect/unprotect option is greyed out. It
becomes available when I click somewhere in the worksheet and then I
can proceed with the manual way or even the macro way. I think it has
something to do with what's on focus in the sheet. I think the sheet
loses the focus when the button is clicked. (If that makes sense)

So, I thought I could be clever by selecting or activating a cell
(range) in each worksheet before protecting/unprotecting, but I still
get the runtime error. The sheets are password protected. I'm
accessing the password via a constant field. But I know it's not a
problem with the password.

Sub ProtectSheets()
Dim ws As Worksheet
For each ws in ActiveWorkbook.Worksheets
ws.Range("A1").Select
ws.Protect constPassword
Next ws
End Sub

The unprotectSheets looks the same other than ws.Unprotect
constPassword

Do you have any suggestions?


--
NikkiBenz
------------------------------------------------------------------------
NikkiBenz's Profile: http://www.excelforum.com/member.php...o&userid=34165
View this thread: http://www.excelforum.com/showthread...hreadid=539284