Share via

Loop worksheets Protect & Unprotect Macros

Anonymous
2011-12-19T14:46:41+00:00

Hello,

I've copied a macro I've found on the excel help to protect multiple sheets in a worksheet which is working perfectly;

Sub Protect()

      ' Loop through all sheets in the workbook

      For i = 1 To Sheets.Count

         ' Activate each sheet in turn.

         Sheets(i).Activate

         response = MsgBox("Do you want to protect this sheet?", vbYesNo)

         If response = vbYes Then

            ActiveSheet.Protect Password:="password", DrawingObjects:=True, _

               Contents:=True, Scenarios:=True

         End If

      Next i

   End Sub

I've now tried to create the same version to unprotect them all simply by changing the Protect to unprotect but it isn't working.

Sub Unprotect()

      ' Loop through all sheets in the workbook

      For i = 1 To Sheets.Count

         ' Activate each sheet in turn.

         Sheets(i).Activate

         response = MsgBox("Do you want to unprotect this sheet?", vbYesNo)

         If response = vbYes Then

              ActiveSheet.Unprotect Password:="updates", DrawingObjects:=True, _

               Contents:=True, Scenarios:=True

         End If

      Next i

   End Sub

Can anybody help please, I love the fact these codes loop through all the spreadsheets. The debug highlights this section; 

ActiveSheet.Unprotect Password:="updates", DrawingObjects:=True, _

               Contents:=True, Scenarios:=True

Thanks in advance for all your help

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2011-12-19T15:23:13+00:00

Try

Sub Unprotect()

      ' Loop through all sheets in the workbook

       For i = 1 To Sheets.Count

         ' Activate each sheet in turn.

          Sheets(i).Activate

         response = MsgBox("Do you want to unprotect this sheet?", vbYesNo)

          If response = vbYes Then

               ActiveSheet.Unprotect Password:="password"

         End If

       Next i

    End Sub

Unprotect only takes one (optional) argument which is password.

Also you may take the question outside the loop if you want to ask the question only once.

Was this answer helpful?

1 person found this answer helpful.
0 comments

6 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2011-12-19T16:49:35+00:00

    So would it be a case of adding something like this,

    ' Allow sorting to be performed on the protected worksheet.

        If ActiveSheet.Protection.AllowSorting = False Then

            ActiveSheet.Protect AllowSorting:=True

    into Sub Protect, or Sub Unprotect.

    Sub Protect()

          ' Loop through all sheets in the workbook

          For i = 1 To Sheets.Count

             ' Activate each sheet in turn.

             Sheets(i).Activate

             response = MsgBox("Do you want to protect this sheet?", vbYesNo)

             If response = vbYes Then

                ActiveSheet.Protect Password:="password", DrawingObjects:=True, _

                   Contents:=True, Scenarios:=True

    ' Allow sorting to be performed on the protected worksheet.

        If ActiveSheet.Protection.AllowSorting = False Then

            ActiveSheet.Protect AllowSorting:=True

             End If

          Next i

       End Sub

    I need to keep the cells locked whilst making them sortable

    Was this answer helpful?

    0 comments
  2. HansV 462.6K Reputation points
    2011-12-19T16:22:44+00:00

    The Protect method has many more arguments, each of which can be set to True or False:

    AllowDeletingColumns

    AllowDeletingRows

    AllowFiltering

    AllowFormattingCells

    AllowFormattingColumns

    AllowFormattingRows

    AllowInsertingColumns

    AllowInsertingHyperlinks

    AllowInsertingRows

    AllowSorting

    AllowUsingPivotTables

    See http://msdn.microsoft.com/en-us/library/aa174285(v=office.11).aspx.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2011-12-19T16:08:10+00:00

    Thanks very much to both of you for your help, very much appreciated.

    Only issue I now have is that I can't sort the information anymore when the spreadsheets are protected, I know how to set this up when I protect sheet by sheet, is there a way to specify what functions can be used when protecting via macro?

    Was this answer helpful?

    0 comments
  4. HansV 462.6K Reputation points
    2011-12-19T15:24:24+00:00

    The Unprotect method has only one argument: the password. Change

                ActiveSheet.Unprotect Password:="updates", DrawingObjects:=True, _

                   Contents:=True, Scenarios:=True

    to

                ActiveSheet.Unprotect Password:="updates"

    Make sure that you use the same password to unprotect that you used to protect the sheets.

    Was this answer helpful?

    0 comments