CODEWITHSUNDEEP
excelvba
Patates Pilées
Patates Pilées

Reputation: 245

Run Worksheet.Unprotect loop through sheet even when multiple sheets are selected

I'm running two macros, protect and unprotect, that loops through the sheet count and protects or unprotects all sheets depending on need. Its an all around sub that I call from different workbooks on different occasions.

Sub UnprotectWorksheets()
Dim i As Integer
' Insert line that says "select whatever sheet" so it cancel inital multiselect?
' (I dont want to use select plz halp :( )
For i = 1 To ActiveWorkbook.Worksheets.Count
Worksheets(i).Unprotect
Next i
End Sub

I've one error when I run it manually and have multiple sheets selected.

Is there a way to add a non memory/time consuming line to avoid this error? multiselect and run

error

debug

Upvotes: 3

Views: 137

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149325

I was trying to avoid using select; – Patates Pilées 59 mins ago

it does not cancel the multisheet selection, il guess il have to stick with selecting a worksheet – Patates Pilées 30 mins ago

Here is one way where we are not using .Select to select the worksheet. See this example. What we are doing here is hiding and unhiding the sheet (if it is not hidden) before unprotecting it. This will automatically cancel the selection as well.

Sub UnprotectWorksheets()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Visible
        Case xlSheetVisible
            On Error Resume Next
            With ws
                .Visible = xlSheetHidden
                .Visible = xlSheetVisible
                .Unprotect
            End With
            On Error GoTo 0
        End Select
    Next ws
End Sub

Note: This method will fail if the workbook structure is protected.

Upvotes: 2

xShen
xShen

Reputation: 572

Sub ProtectWorksheets()
Dim i As Integer
Worksheets(1).Select
For i = 1 To ActiveWorkbook.Worksheets.Count
    Worksheets(i).Protect
Next i
End Sub

Upvotes: 0

Related Questions