I'm trying to run macros on protected worksheets and I have the following code:

VB:

 
Public UniversalPassword As String 
Sub AccelCurrentYours() 
     
    UniversalPassword = "Tampa" 
     
    ActiveSheet.Unprotect Password:=UniversalPassword 
    Sheets("Support Calculations").Unprotect Password:=UniversalPassword 
     
     '[code to be run]
     
    ActiveSheet.Protect Password:=UniversalPassword 
    Sheets("Support Calculations").Protect Password:=UniversalPassword 
End Sub 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



The problem with the code is that VBA will not acknowledge the "unprotect" commands above. I've double-checked to make sure the passwords were correct to begin with but when the code runs the "unprotect" commands, I get an error within the excel spreadsheet saying that I'm trying to run an operation on a protected worksheet that needs to be unprotected. I'm not sure what I'm missing above and what's really odd is that similar code was working before. I'm not sure if I accidentally changed a property or something of that nature.

Thanks!

Is there a way to easily unprotect all the worksheets in a workbook? For
example, I have a workbook with 20 worksheets (one for each employee). The
worksheets have been individually protected (except for the select cells into
which I enter data. Now with the new year, I would like to unprotect all the
worksheets so I can change the (previously protected) field that contains
the year and then reprotect all the worksheets. Thanks.

Hello, I am using the VBA code below in my spreadsheet to automatically protect/unprotect all of my worksheets at once. The code works great, but I was hoping to tweak it slightly so that when I'm prompted to type in the password, it will show the password as **** as I type it (as opposed to displaying the actual password). Does anyone know of a way to do this? (I apologize that I don't know how to insert the VBA code so that it is in the special gray box that I have seen on other posts - this website was the origin of the code, which might be easier to view: http://www.vbaexpress.com/kb/getarticle.php?kb_id=142 ) . . .


Option Explicit

Sub ProtectAll()

Dim wSheet As Worksheet
Dim Pwd As String

Pwd = InputBox("Enter your password to protect all worksheets", "Password Input")
For Each wSheet In Worksheets
wSheet.Protect Password:=Pwd
Next wSheet

End Sub

Sub UnProtectAll()

Dim wSheet As Worksheet
Dim Pwd As String

Pwd = InputBox("Enter your password to unprotect all worksheets", "Password Input")
On Error Resume Next
For Each wSheet In Worksheets
wSheet.Unprotect Password:=Pwd
Next wSheet
If Err 0 Then
MsgBox "You have entered an incorect password. All worksheets could not " & _
"be unprotected.", vbCritical, "Incorect Password"
End If
On Error Goto 0

End Sub

Currently, I'm using the following code on an Active X command button to unprotect all worksheets within my workbook ...

Code:

Sub UnProtectSheets()
  Dim ws As Worksheet
  For Each ws In ThisWorkbook.Sheets
       ws.Unprotect Password:="ana"
  Next ws
End Sub

however, it doesn't seem to be working.

Have I missed something obvious ?

Chris

I installed these macros on a sample workbook and they function properly. When I run the protect macro all the pages are protected properly and visa versa. So how does the password function work?

Code located at: http://www.excelforum.com/excel-prog...ml#post2285150

-- Is it possible to protect/unprotect all worksheets in a workbook at once
instead of having to do each individually if the password is the same?

News Gal

I used the following macro from an earlier posting to protect multiple
worksheets in a workbook and it worked great. (Thank you Kevin Backmann!!!)

Now I would like a macro to do the reverse - that is unprotect all
worksheets. I tried the same macro changing every instance of the word
Protect to Unprotect, but that didn't work. What can I do to make this work?

Thanks. (I've copied and pasted Kevin's earlier posting below

-------------------------------------------

"The following macro cycless through all the worksheets in the active
workbook, protecting them and assigning the password of "Password"

Sub ProtectAll()

Dim wb As Workbook
Dim ws As Worksheet

Set wb = ActiveWorkbook

For Each ws In wb.Worksheets
ws.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True, _
Password:="Password"
Next ws

Set wb = Nothing
Set ws = Nothing

End Sub

Place it in a VBA module by pressing Alt+F11, click INSERT on the menu and
select MODULE. You can either type the code above or cut and paste it. To
run the macro select TOOLS on the menu, click MACRO and then select MACROS.
The macro will be in the list of available macros. You can select it and
then click the RUN command button."

Hi, I have these macro's below that protect/unprotect all worksheets in the
book. How do I get them to only work on the sheets I selected?

TIA

Todd


Sub ProtectAll()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Protect Password:=""
Next ws
End Sub

Sub UnprotectAll()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Unprotect Password:=""
Next ws
End Sub

I am looking for a way to protect and unprotect all worksheets with a password through a macro. I have excel 2007.
Thanks

I have all my codes working and protected the cells in the 2 worksheets, now it is telling me that there is a run time eror '1004' for the PasteSpecial method of Range class failed.

I have put in the codes to unprotect/protect the sheet back and if i leave them unprotected it works fine. I am using a command button to copy the data from one sheet to the second sheet and it fails when it gets to the second sheet. The debug highlites the one below that is in Italics. Cannot find out how to make it work.

Code:

Private Sub CommandButton11_Click()
With Worksheets("Activity Entries")
.Range("A3:AB3").Copy
End With
With Worksheets("Email to Scheduler_s").Unprotect
 Worksheets("Email to Scheduler_s").Range("B2").PasteSpecial Paste:=xlPasteValues
 Worksheets("Email to Scheduler_s").Range("B2").PasteSpecial Paste:=xlPasteFormats
With Worksheets("Email to Scheduler_s").Protect
End
End
End With 'sheet2
End 'sheet1
End With
End Sub