Unprotect all worksheets vba doesn't work

Apple08

Active Member
Joined
Nov 1, 2014
Messages
404
Hi All

I have a workbook with a number of worksheets which might have been protected or not protected by users. I have created the macro below to unprotect all worksheets, however I got the error message:

Run-time error '1004':
Mthod 'Unprotect' of object'_Worksheet' failed


VBA Code:
Sub Unprotect()

Dim ws As Worksheet

    For Each ws In Worksheets
        ws.Unprotect
    Next ws

End Sub

Please could anyone help? Many thanks.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
865
Office Version
  1. 365
Platform
  1. Windows
Are any of the worksheets protected with passwords? That will give you the error.
 

Apple08

Active Member
Joined
Nov 1, 2014
Messages
404
Thanks Jeff, it makes sense to me now. Please am I able to unprotect specific worksheets from the workbook? I require to unprotect 'Sheet 1" to "Sheet 5" as they are only protected without a password.
 

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
865
Office Version
  1. 365
Platform
  1. Windows
Update the names of the sheets I used to your actual sheet names.
VBA Code:
Sub Unprotect()

   Dim S As Variant

   For Each S In Array("Sheet 1", "Sheet 2", "Sheet 3", "Sheet 4", "Sheet 5")
      Worksheets(S).Unprotect
   Next S

End Sub
 
Solution

Forum statistics

Threads
1,136,968
Messages
5,678,862
Members
419,787
Latest member
juanam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top