Archive of Mr Excel Message Board


Back to Excel VBA archive index
Back to archive home

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.

Check out our Excel VBA Resources

Re: Protect Sheet Macro

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


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.