Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Request password during Protect macro
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Request password during Protect macro
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Request password during Protect macro
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Request password during Protect macro
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Request password during Protect macro
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Request password during Protect macro
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/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Request password during Protect 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 : ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to allow auto filter after running password protect | Excel Discussion (Misc queries) | |||
Macro to re-protect sheet w/ original password after pasting valuesin new book | Excel Worksheet Functions | |||
Password Protect Macro - Compile Error | Excel Discussion (Misc queries) | |||
Code to protect/unprotect a sheet using a macro with password | Excel Discussion (Misc queries) | |||
Add protect worksheet password in macro | Excel Worksheet Functions |