Add security to run worksheet_unprotect loop trough sheet even when mutiple sheet are selected

user_13964158 0
Viewed 63

Sup boys and gurls, im back again

Simple question to get my code as thicc as possible. Im running 2 macro, protect and unprotect, that loop trough the sheet count and protect or unprotect all sheets depending on needed; its an all around sub that im calling from different wb on different occasion but its not every-bulletproof yet.

Ive got one error when i run it manualy and have multiple sheet selected (like its trying to run the loop one every sheet on the multi-selection, multi-select not being a sheet it create an objet problem)

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

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

multiselect and run error debug

vba excel
2 Answers

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.

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