Macro to Unprotect and Protect Password Protected Sheet

Posted by Mandy B on April 03, 2001 12:50 AM

I have a problem where users unprotect sheets in my workbook and delete or write over formula's. Is there a macro where it can uprotect a passworded protected sheet, run a macro then password protect the sheet again?

Thanks in advance,

Mandy

Posted by David Hawley on April 03, 2001 1:05 AM

Hi Mandy

Here are two examples for you. The first Unprotects ALL sheets and places the number 100 in cells A1:A10 of each sheet. The second acts on only the one sheet.

Sub UnprotectAllSheets()
Dim Sht As Worksheet

For Each Sht In ThisWorkbook.Worksheets
Sht.Unprotect Password:="secret"
Sht.Range("A1:A10") = 100
Next

For Each Sht In ThisWorkbook.Worksheets
Sht.Protect Password:="secret"
Next

End Sub

Sub UnprotectOneSheet()
Sheets("Sheet1").Unprotect Password:="secret"
Sheets("Sheet1").Range("A1:A10") = 100
Sheets("Sheet1").Protect Password:="secret"
End Sub

Dave

OzGrid Business Applications