MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Protect Sheet Macro


Posted by Wingz on August 05, 2001 11:39 AM

Can anyone give me a with specifying a pre-set password when writing a protect sheet macro. For example, let's say I'd like the password to be "dog". What syntax is needed to achieve this? If I just record a macro and go through the steps of protecting a sheet AND entering the passowrd, the macro sets the password to ""(null) so simply clicking unprotect, unprotects the sheet. Any help would be cool.


Posted by Dax on August 05, 2001 2:20 PM

These two bits of code protect and unprotect a specified sheet with a specified password.

HTH,
Dax.

Sub ProtectSheet()
Dim shtAnySheet As Worksheet
Dim sPassword As String
sPassword = "dog"
Set shtAnySheet = Worksheets("Sheet1")
shtAnySheet.Protect sPassword
End Sub

Sub UnprotectSheet()
Dim shtAnySheet As Worksheet
Dim sPassword As String
sPassword = "dog"
Set shtAnySheet = Worksheets("Sheet1")
shtAnySheet.Unprotect sPassword
End Sub