Protecting multiple sheets

[Robert Crandal]

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?

 

[Benito Merino]

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

 

[Mike H]

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

 

[Mike H]

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

 

[Mike Fogleman]

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