MrExcel Consulting
Your One Stop for Excel Tips & Solutions

Protect and unprotect worksheet using macros/VBA

Posted by Kevin W. on December 19, 2000 5:58 PM

Is there a way, using VBA in Excel to construct a macro that will turn the worksheet protection off, so spreadsheet input can occur then turn the worksheet protection back on without the user inputting the password or being able to see the password protection in the macro coding? Thanks

Posted by Duane Kennerson on December 19, 2000 8:42 PM

Here are two macro's that I use to lock and unlock a sheet.
You can put anypassword in, I use "DAK". Once you write the marcos use can use
unlock sheet at the begining of the code and lock sheet at the end.
Hope this helps...

Public Sub unlocksheet()
'unlock the worksheet

On Error Resume Next
ActiveSheet.unprotect password:="DAK"

End Sub
Public Sub locksheet()
'lock the worksheet

On Error Resume Next
ActiveSheet.PROTECT password:="DAK"

End Sub