MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Unprotecting a sheet through a macro


Posted by jamesman on March 23, 2000 8:15 PM

Hi,
I've got a worksheet that I need to have protected and I need to have a password on it. Now, I'm running a macro in which I need to temporarily unprotect the sheet and then re-protect it later in the function. So if I protect it without a password, all works fine but if I protect it with a password, it won's run. I'm using the "ActiveSheet.Unprotect" at the beginning of my macro and what I don't know is how to tell the macro to use my password in the process.
Any suggestions will be greatly apprecitated.
James


Posted by Simon on March 24, 2000 3:25 AM

I use this at the beggining of a procedure

sheets("sheetx").Unprotect password:="hello"

and this near the end

sheets("sheetx").Unprotect password:="hello"

Posted by Simon on March 24, 2000 3:26 AM

made a slight error; here's the correct one

Posted by Mark S. Maerz on March 24, 2000 6:43 AM


James,

I have read the other replies and would like to add what I have done in my situation. I have pasted my code below.

' Unprotect Worksheets
Sheets("Previous Year Actual").Unprotect ("mark")
Sheets("Current Year Actual").Unprotect ("mark")
' Copy Current Year Actual and Roll into Previous Year Actual
For XRanges = 1 To 15
Sheets("Previous Year Actual").Range(CopyRanges(XRanges)).Value = _
Sheets("Current Year Actual").Range(CopyRanges(XRanges)).Value
Next XRanges
' Protect Worksheets
Sheets("Previous Year Actual").Select
ActiveSheet.Protect Password:="mark", DrawingObjects:=True, Contents:=True, Scenarios _
:=True
Range("F5").Select
Sheets("Current Year Actual").Select
ActiveSheet.Protect Password:="mark", DrawingObjects:=True, Contents:=True, Scenarios _
:=True
Range("F5").Select

Posted by Jamesman on March 24, 2000 7:18 AM

Thank you both

Hey Mark, Simon,
thank you both for posting here. That is exactly what I needed.

Thanks again,
Jamie