Protect

Humble Pie · May 25th, 2002, 05:13 PM

How can i unprotect and then protect a
worksheet with a password without making the password available to others.

Ivan F Moala · May 25th, 2002, 07:51 PM

Quote:

On 2002-05-25 16:13, Humble Pie wrote:
How can i unprotect and then protect a
worksheet with a password without making the password available to others.

You will have to do this via VBA
Look @ Help files avail Via F1 while
in the VBA Editor.
1) message box method
2) protect method & unprotect method

So basically what you will be doing is bringing up a messagebox to ask the user that .....[your setup] will protect/unprotect the sheet/workbook.

Should be examples in help file.

If you get stuck then post fro Help

Joe Was · May 25th, 2002, 09:13 PM

In the sheet module put:

Private Sub Worksheet_Change(ByVal Target As Range)
'
' Macro by Joseph S. Was
'
Application.EnableEvents = False

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="admin"
Application.EnableEvents = True
End Sub

Then in a module add the unlock code and attach your code, if any, before the End Sub.

Note: You can run the un-lock code from a Form-Button or use a hot-key with Macros-Macro-select macro-Options-select your key.

Sub myUnLock()
'This code will unprotect the sheet.
Application.EnableEvents = False
Application.DisplayAlerts = False
ActiveSheet.Unprotect ("admin")

'Add optional code here!

Application.EnableEvents = True
Application.DisplayAlerts = True

End Sub

Then run your code out of the unlock sub, the first macro will password lock the code behind you automatically. You can make the password anything you want. To help hide your password you can password lock your macros form the VBA editor pull-down menu!

Note: this uses hard coded password protection which will decrease the security of the protection. But then again a password does not really deter someone from getting in if they want to, like a door lock does not stop some one from getting in a window, but does stop the causal passer-by from coming in. JSW

[ This Message was edited by: Joe Was on 2002-05-25 20:19 ]

[ This Message was edited by: Joe Was on 2002-05-25 20:23 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 25th, 2002, 05:13 PM	#1
Humble Pie New Member Join Date: May 2002 Posts: 3	How can i unprotect and then protect a worksheet with a password without making the password available to others.

May 25th, 2002, 09:13 PM	#3
Joe Was MrExcel MVP Join Date: Feb 2002 Location: Central Florida, USA Posts: 7,541	In the sheet module put: Private Sub Worksheet_Change(ByVal Target As Range) ' ' Macro by Joseph S. Was ' Application.EnableEvents = False ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="admin" Application.EnableEvents = True End Sub Then in a module add the unlock code and attach your code, if any, before the End Sub. Note: You can run the un-lock code from a Form-Button or use a hot-key with Macros-Macro-select macro-Options-select your key. Sub myUnLock() 'This code will unprotect the sheet. Application.EnableEvents = False Application.DisplayAlerts = False ActiveSheet.Unprotect ("admin") 'Add optional code here! Application.EnableEvents = True Application.DisplayAlerts = True End Sub Then run your code out of the unlock sub, the first macro will password lock the code behind you automatically. You can make the password anything you want. To help hide your password you can password lock your macros form the VBA editor pull-down menu! Note: this uses hard coded password protection which will decrease the security of the protection. But then again a password does not really deter someone from getting in if they want to, like a door lock does not stop some one from getting in a window, but does stop the causal passer-by from coming in. JSW [ This Message was edited by: Joe Was on 2002-05-25 20:19 ] [ This Message was edited by: Joe Was on 2002-05-25 20:23 ]