Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to prompt for password to unprotect worksheets
I've got the following vb code in an Excel macro to unprotect all worksheets
in a document: Sub UnprotectAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To Worksheets.Count Worksheets(n).Unprotect Password:="password" Next n Application.ScreenUpdating = True End Sub The code is attached to a command button on the first worksheet. When the button is selected, I'd like a prompt to appear to ask the user to input the relevant password. If the password entered does not match what has been specified in the code, the sheets cannot be unprotected. How can I do this? Also, is there any way of disabling the sheet protection option on the menu bar: Tools\Protection\Unprotect Sheet? Many thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to prompt for password to unprotect worksheets
Sarah,
A sumple way would be this Sub UnprotectAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To Worksheets.Count response = InputBox("Enter password for sheet " & n) On Error Resume Next Worksheets(n).Unprotect Password:=response Next n Application.ScreenUpdating = True End Sub For your second question you need 2 bits of code. One in the workbook open event and one in the before close event. Private Sub Workbook_BeforeClose(Cancel As Boolean) With Application.CommandBars("Tools") With .Controls("&Protection") .Controls("&Unprotect Sheet...").Enabled = True End With End With End Sub Private Sub Workbook_Open() With Application.CommandBars("Tools") With .Controls("&Protection") .Controls("&Unprotect Sheet...").Enabled = False End With End With End Sub Mike "Sarah (OGI)" wrote: I've got the following vb code in an Excel macro to unprotect all worksheets in a document: Sub UnprotectAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To Worksheets.Count Worksheets(n).Unprotect Password:="password" Next n Application.ScreenUpdating = True End Sub The code is attached to a command button on the first worksheet. When the button is selected, I'd like a prompt to appear to ask the user to input the relevant password. If the password entered does not match what has been specified in the code, the sheets cannot be unprotected. How can I do this? Also, is there any way of disabling the sheet protection option on the menu bar: Tools\Protection\Unprotect Sheet? Many thanks in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to prompt for password to unprotect worksheets
On 26 Mar, 09:43, Mike H wrote:
Sarah, A sumple way would be this Sub UnprotectAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To Worksheets.Count * * response = InputBox("Enter password for sheet " & n) * * On Error Resume Next * * Worksheets(n).Unprotect Password:=response Next n Application.ScreenUpdating = True End Sub For your second question you need 2 bits of code. One in the workbook open event and one in the before close event. Private Sub Workbook_BeforeClose(Cancel As Boolean) With Application.CommandBars("Tools") * * * * With .Controls("&Protection") * * * * * * .Controls("&Unprotect Sheet...").Enabled = True * * * * End With * * End With End Sub Private Sub Workbook_Open() With Application.CommandBars("Tools") * * * * With .Controls("&Protection") * * * * * * .Controls("&Unprotect Sheet...").Enabled = False * * * * End With * * End With End Sub Mike "Sarah (OGI)" wrote: I've got the following vb code in an Excel macro to unprotect all worksheets in a document: Sub UnprotectAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To Worksheets.Count * * Worksheets(n).Unprotect Password:="password" Next n Application.ScreenUpdating = True End Sub The code is attached to a command button on the first worksheet. *When the button is selected, I'd like a prompt to appear to ask the user to input the relevant password. *If the password entered does not match what has been specified in the code, the sheets cannot be unprotected. *How can I do this? Also, is there any way of disabling the sheet protection option on the menu bar: Tools\Protection\Unprotect Sheet? Many thanks in advance.- Hide quoted text - - Show quoted text - Hi Sarah, Alternately what you can do is have a UserForm on to prompt for the password, and then in the properties of the text box you can change the PasswordChar to * With this approach you will also be able to have usernames with specific passwords to open specific parts of the document. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to protect/unprotect with password | Excel Programming | |||
Password Applied to All Worksheets Confrimation Prompt | Excel Programming | |||
Password Applied to All Worksheets Confrimation Prompt | Excel Programming | |||
Protecting Multiple sheets with prompt for password to unprotect | Excel Programming | |||
unprotect more than one password in macro | Excel Programming |