Protect Excel sheets with Password Using VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to prompt for a password that covers all sheets when using VBA
to protect multiple sheets in a workbook?
 
This'll protect all the unprotected worksheets with the same password:

Option Explicit
Sub testme01()
Dim myPwd As String
Dim wks As Worksheet

myPwd = InputBox(prompt:="what's the password, kenny?")

If Trim(myPwd) = "" Then
Exit Sub
End If

For Each wks In ThisWorkbook.Worksheets
If wks.ProtectContents _
Or wks.ProtectDrawingObjects _
Or wks.ProtectScenarios Then
'already protected
Else
wks.Protect Password:=myPwd
End If
Next wks

End Sub
 
Thanks Dave - really appreciate the help.

Dave Peterson said:
This'll protect all the unprotected worksheets with the same password:

Option Explicit
Sub testme01()
Dim myPwd As String
Dim wks As Worksheet

myPwd = InputBox(prompt:="what's the password, kenny?")

If Trim(myPwd) = "" Then
Exit Sub
End If

For Each wks In ThisWorkbook.Worksheets
If wks.ProtectContents _
Or wks.ProtectDrawingObjects _
Or wks.ProtectScenarios Then
'already protected
Else
wks.Protect Password:=myPwd
End If
Next wks

End Sub
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Protection 2
password protect individual sheets 1
Sheet protection complications 4
Controlling worksheet protection via VBA 0
How to bypass password when pasting FROM a protected workbook 3
prompt password for a worksheet 5
Modifying a locked/protected sheet 3
Problem using VBA for Excel to protect a Workbook with a password. 4

Back
Top