MrExcel Publishing
Your One Stop for Excel Tips & Solutions

protection


Posted by Rayn on June 04, 2001 4:12 PM

Hi

These are 2 macros one that protects all the formulas in a sheet
and another that unprotects them. can someone please help add a password protection
pop up box, so when i press the protect macro a box appears where i can put a password
and when i press the inprotect button a box will appear asking me to deactivate the password.

Thank you
Regards
Rayn


Sub Protect()
crntcell = Selection.Address
ActiveSheet.unprotect
Cells.Select
Selection.Locked = False
On Error GoTo finish
Selection.SpecialCells(xlFormulas, 23).Select
Selection.Locked = True
finish:

ActiveSheet.Protect Contents:=True
Range(crntcell).Select

End Sub

Sub unprotect()
crntcell = Selection.Address
ActiveSheet.unprotect
Cells.Select
On Error GoTo finish

Selection.SpecialCells(xlsformulas, 23).Select
Selection.Locked = False
finish:

Range(crntcell).Select

End Sub


Posted by Barrie Davidson on June 05, 2001 9:13 AM

Hi Rayn, I added input message boxes to get your password. I am assuming your password is string. If not, take out "As String" form the variable declaration. Also, I took the liberty of changing the name of your macro so that the procedure name did not match a name used in VBA code (protect and unprotect are names used in VBA). This was done for my benefit when I tested the code, you can change it back if you want.

Sub Pro_tect()
Dim pw As String
pw = InputBox("Enter Password")
crntcell = Selection.Address
ActiveSheet.Unprotect (pw)
Cells.Select
Selection.Locked = False
On Error GoTo finish
Selection.SpecialCells(xlFormulas, 23).Select
Selection.Locked = True
finish:

ActiveSheet.Protect password:=pw, Contents:=True
Range(crntcell).Select

End Sub

Sub un_protect()
Dim pw As String
crntcell = Selection.Address
ActiveSheet.Unprotect (pw)
Cells.Select
On Error GoTo finish

Selection.SpecialCells(xlsformulas, 23).Select
Selection.Locked = False
finish:

Range(crntcell).Select

End Sub

Regards,
Barrie

Posted by rayn on June 05, 2001 6:21 PM

Barrie that works

Thank you a great deal !!!!!!!!!!!!!!!