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


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

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
--

"Gavin" wrote in message
...

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


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

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


In article ,
Gavin wrote:

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.

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


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Use a user form. Userform Textboxes have character masking (set the
PasswordChar property to the character you want).


In article ,
Gavin wrote:

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?

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?


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

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 :

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com