Request password during Protect macro

  • Thread starter Thread starter Gavin
  • Start date Start date
G

Gavin

Hi.
I have a macro that unprotects and protects a Sheet & Workbook, but I
want excel to prompt the user to provide the password as if they had
selected protect/unprotect from the menu. How do I do this?
Thanks for any help.
Regards
Gavin
 
Hi Gavin,

You could use something like

Sub Pwd()
Dim Pwd, Res
Pwd = "abracadabra"
Res = InputBox("Please Enter Password")
If Res <> Pwd Then Exit Sub
End Sub

You can either call this Sub from your Unprotect macr or includenthe code
within the macro.

HTH

Don
 
One way:

Dim pWord As Variant
Dim wkSht As Worksheet
Set wkSht = ActiveSheet ' for testing
With wkSht
If .ProtectContents Then
Do
pWord = Application.InputBox( _
Prompt:="Password: ", _
Title:="Unprotect Sheet", _
Type:=2)
If pWord = False Then Exit Sub 'User clicked Cancel
On Error Resume Next
wkSht.Unprotect pWord
On Error GoTo 0
Loop Until .ProtectContents = False
End If
End With
 
Thanks for the help.

I know that what you suggest would work, but I wasn't wanting to have
to use Inputboxes. They don't have character masking and I didn't want
to get involved in having to confirm the input of a PW when switching
protection on.

I was hoping that Excel would do that that for me like when you turn
protection on and off through the menus.

In fact, can I not trigger menu actions from VBA code?

Thanks again
Gavin
 
Use a user form. Userform Textboxes have character masking (set the
PasswordChar property to the character you want).
 
At the risk of sounding like an idiot...
I've created a userform for the use to enter a password, but how do
send the password back from the text box to my macro?
 
Got it! The Dialog boxes toggle protection on and off. If I don't sen
any agruments then if protection was on, excel asks for a password an
thurns protection off. If protection was off, then excel asks for
password, asks for confirmation of the password, and then turn
protection on.
Fantastic :
 
Back
Top