Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protecting multiple sheets
If my workbook has 20 sheets, I would like to write a script
that protects all 20 sheets at once, but it only asks the user for a password once. I basically want to display the same dialog box that appears when someone presses the "Protect Sheet" button. Is this possible? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protecting multiple sheets
On 7 ene, 11:11, "Robert Crandal" wrote:
If my workbook has 20 sheets, I would like to write a script that protects all 20 sheets at once, but it only asks the user for a password once. *I basically want to display the same dialog box that appears when someone presses the "Protect Sheet" button. Is this possible? Hello. One way is: Sub protect() For Each ws In ActiveWorkbook.Sheets ws.protect Password:="pwsheet" Next End Sub Sub unprotect() For Each ws In ActiveWorkbook.Sheets ws.unprotect Password:="pwsheet" Next End Sub Regards, Benito Barcelona, spain |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protecting multiple sheets
Hi,
Thiis uses an input box and provides a toggle. If a sheet is protected it unprotects and vice versa Sub ProtectAll() Dim MyPass As String MyPass = InputBox("Enter password for sheets", "Sheet Protection") For x = 1 To Worksheets.Count If Sheets(x).ProtectContents Then Sheets(x).Unprotect Password:="MyPass" Else Sheets(x).Protect Password:="MyPass" End If Next End Sub Mike "Robert Crandal" wrote: If my workbook has 20 sheets, I would like to write a script that protects all 20 sheets at once, but it only asks the user for a password once. I basically want to display the same dialog box that appears when someone presses the "Protect Sheet" button. Is this possible? . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protecting multiple sheets
Hi,
If you want to do it with a 'masked' inputbox the 'usual' way is to create a userform but here's a way to do it with a cistom input box. Credit Ivan F Moala. Put this in a module Option Explicit ''///////////////////////////////////////////////////////////////// ''// 25 May 2003 // ''// Amended Ivan F Moala ''// Call with myresponse=InPutBoxPwd(etc ''// from any module ''///////////////////////////////////////////////////////////////// Public Declare Function GetActiveWindow _ Lib "user32" () _ As Long Public Declare Function FindWindowEx _ Lib "user32" _ Alias "FindWindowExA" ( _ ByVal hWnd1 As Long, _ ByVal hWnd2 As Long, _ ByVal lpsz1 As String, _ ByVal lpsz2 As String) _ As Long Public Declare Function SendMessage _ Lib "user32" _ Alias "SendMessageA" ( _ ByVal hwnd As Long, _ ByVal wMsg As Long, _ ByVal wParam As Long, _ lParam As Any) _ As Long Public Declare Function SetTimer _ Lib "user32" ( _ ByVal hwnd As Long, _ ByVal nIDEvent As Long, _ ByVal uElapse As Long, _ ByVal lpTimerFunc As Long) _ As Long Public Declare Function KillTimer _ Lib "user32" ( _ ByVal hwnd As Long, _ ByVal nIDEvent As Long) _ As Long Public Declare Function GetForegroundWindow _ Lib "user32" () _ As Long Private Const nIDE As Long = &H100 Private Const EM_SETPASSWORDCHAR = &HCC Private hdlEditBox As Long Private Fgrndhdl As Long Public Function TimerFunc( _ ByVal hwnd As Long, _ ByVal wMsg As Long, _ ByVal nEvent As Long, _ ByVal nSecs As Long) As Long Dim hdlwndAct As Long '// Do we have a handle to the EditBox If hdlEditBox 0 Then Exit Function '// Get the handle to the ActiveWindow hdlwndAct = GetActiveWindow() '// Get the Editbox handle hdlEditBox = FindWindowEx(hdlwndAct, 0, "Edit", "") '// Set the password character for the InputBox SendMessage hdlEditBox, EM_SETPASSWORDCHAR, Asc("*"), ByVal 0 End Function Public Function InPutBoxPwd(fPrompt As String, _ Optional fTitle As String, _ Optional fDefault As String, _ Optional fXpos As Long, _ Optional fYpos As Long, _ Optional fHelpfile As String, _ Optional fContext As Long) As String Dim sInput As String '// Initialize hdlEditBox = 0 Fgrndhdl = GetForegroundWindow '// Windows-Timer SetTimer Fgrndhdl, nIDE, 100, AddressOf TimerFunc '// Main InputBox If fXpos Then sInput = InputBox(fPrompt, fTitle, fDefault, fXpos, fYpos, fHelpfile, fContext) Else sInput = InputBox(fPrompt, fTitle, fDefault, , , fHelpfile, fContext) End If '// Kill the correct Timer KillTimer Fgrndhdl, nIDE '// Pass result InPutBoxPwd = sInput End Function Then call it with my code Sub ProtectAll() Dim MyPass As String Dim x As Long MyPass = InPutBoxPwd("Enter password for sheets", "Sheet Protection") For x = 1 To Worksheets.Count If Sheets(x).ProtectContents Then Sheets(x).Unprotect Password:="MyPass" Else Sheets(x).Protect Password:="MyPass" End If Next End Sub Mike "Robert Crandal" wrote: If my workbook has 20 sheets, I would like to write a script that protects all 20 sheets at once, but it only asks the user for a password once. I basically want to display the same dialog box that appears when someone presses the "Protect Sheet" button. Is this possible? . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protecting multiple sheets
Robert, Excel is not going to give you the password entered into it's
built-in dialog box to re-use for each sheet. The simplest method is to use an inputbox to get the password like the following code: Sub protect() Dim w As Worksheet Dim pwd As String pwd = InputBox("Password", "Password Protect All Sheets") For Each w In Worksheets w.protect Password:=pwd Next End Sub If you need to provide the protection options, rather than just full protection, as the above code does, then you would need to build your own userform similar to what the built-in has, and write code for each option. A lot of extra work. Mike F "Robert Crandal" wrote in message ... If my workbook has 20 sheets, I would like to write a script that protects all 20 sheets at once, but it only asks the user for a password once. I basically want to display the same dialog box that appears when someone presses the "Protect Sheet" button. Is this possible? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
protecting multiple sheets | Excel Programming | |||
Protecting Multiple Sheets | Excel Discussion (Misc queries) | |||
Protecting Multiple Sheets | Excel Discussion (Misc queries) | |||
Protecting multiple sheets | Excel Discussion (Misc queries) | |||
Protecting and Unprotecting multiple sheets | Excel Programming |