VBA: Creating array of sheetnames based on the sheets currently selected

[adamdynamic]

Hi,

I've scoured the internet looking for a solution for this, I'm hoping someone here can help me?

I am writing a macro that allows users to protect multiple sheets at once (rather than protecting them one at a time). For the current macro to work I need to pass it an array of worksheet names e.g. ("Sheet1","Sheet2","Sheet3") which the macro then iterates through.

Is there a way to generate an array of sheet names like this based on the sheets that the user had selected immediately prior to running the code? The names and number of tabs selected will vary, if anyone can suggest a way to do this (or alternatively, a better way to approach the problem) that would be enormously appreciated!

If more information would be useful, please let me know!

Thanks,

Adam

 

[VoG]

Try like this

Dim ws As Worksheet
For Each ws In ActiveWindow.SelectedSheets
    ws.Protect
Next ws

 

[adamdynamic]

Try like this

Dim ws As Worksheet
For Each ws In ActiveWindow.SelectedSheets
    ws.Protect
Next ws

Hey,

Thanks for the fast response! I've tried to implement your suggestion into my code, for some reason it works perfectly when I only select one sheet, however when I try to select more than one sheet and run the code I get 'run-time error 1004, method 'Protect' of object '_worksheet' failed'. Is there an assumption I'm making somewhere or something obvious that I'm overlooking as to why this wouldn't work?

I've been going around in circles with this for most of the afternoon so any assistance would be much appreciated!

Thanks,

Adam

Sub ProtectAllSelectedSheets(control As IRibbonControl)
    
'Protects all selected sheets according to the options returned by the ProtectSheetOptions UserForm
Const FN_NAME As String = "ProtectAllSelectedSheets"


On Error GoTo catch


    Dim ws As Worksheet
    
    ' Call the UserForm to populate the PROTECT_... boolean variables
    ProtectSheetOptions.Show
    
    For Each ws In ActiveWindow.SelectedSheets
        Call ProtectWorksheet(ws.Name)
    Next ws


finally:
    ' Clear the stored password
    PROTECT_SHEETS_PASSWORD = ""


    ' Unload the UserForms from memory
    Unload ProtectSheetOptions
    Unload ProtectSheetPasswordConfirm
    Exit Sub


catch:
MsgBox FN_NAME, vbCritical, "Error"
Resume finally
    
End Sub


Private Sub ProtectWorksheet(ws As Worksheet)


'Protects individual worksheets based on the values of global variables set by the ProtectSheetOptions UserForm
Const FN_NAME As String = "ProtectWorksheet"


On Error GoTo catch
    
    Sheets(ws).Protect _
        Password:=PROTECT_SHEETS_PASSWORD, _
        DrawingObjects:=PROTECT_EDIT_OBJECTS, _
        Contents:=True, _
        Scenarios:=PROTECT_EDIT_SCENARIOS, _
        AllowFormattingCells:=PROTECT_FORMAT_CELLS, _
        AllowFormattingColumns:=PROTECT_FORMAT_COLS, _
        AllowFormattingRows:=PROTECT_FORMAT_ROWS, _
        AllowInsertingColumns:=PROTECT_INSERT_COLS, _
        AllowInsertingRows:=PROTECT_INSERT_ROWS, _
        AllowInsertingHyperlinks:=PROTECT_INSERT_LINKS, _
        AllowDeletingColumns:=PROTECT_DELETE_COLS, _
        AllowDeletingRows:=PROTECT_DELETE_ROWS, _
        AllowSorting:=PROTECT_SORT, _
        AllowFiltering:=PROTECT_FILTER, _
        AllowUsingPivotTables:=PROTECT_USE_PIVOTS
    
    If PROTECT_SELECT_LOCKED = False And PROTECT_SELECT_UNLOCKED = False Then
        ' If neither options are True, then no cells may be selected
        Sheets(ws).EnableSelection = xlNoSelection
    ElseIf PROTECT_SELECT_LOCKED = False Then
        ' If '.._LOCKED' is False then '.._UNLOCKED' is FALSE by default
        Sheets(ws).EnableSelection = xlUnlockedCells
    ElseIf PROTECT_SELECT_LOCKED = True And PROTECT_SELECT_UNLOCKED = True Then
        Sheets(ws).EnableSelection = xlNoRestrictions
    Else
        ' No more options available, included as a fail-safe
        GoTo catch
    End If
    
finally:
    Exit Sub


catch:
MsgBox FN_NAME, vbCritical, "Error"
Resume finally


End Sub

 

[VoG]

I think that should be

Private Sub ProtectWorksheet(ws As String)



'Protects individual worksheets based on the values of global variables set by the ProtectSheetOptions UserForm
Const FN_NAME As String = "ProtectWorksheet"


On Error GoTo catch
    
    Sheets(ws).Protect

 

[adamdynamic]

Ah, actually I posted the code half-way through a refactoring attempt, I've tried it passing both a string and a worksheet to the sub and neither seem to work. There doesn't seem to be a way to edit my previous post, so I've added the current code below. I've tried again to get it working, if anyone can point out what I'm doing wrong that would be greatly appreciated! Thanks, Adam

Sub ProtectAllSelectedSheets(control As IRibbonControl)
    
'Protects all selected sheets according to the options returned by the ProtectSheetOptions UserForm
Const FN_NAME As String = "ProtectAllSelectedSheets"


On Error GoTo catch


    Dim ws As Worksheet
    
    ProtectSheetOptions.Show
    
    For Each ws In ActiveWindow.SelectedSheets
        Call ProtectWorksheet(ws)
    Next ws


finally:
    ' Clear the stored password
    PROTECT_SHEETS_PASSWORD = ""


    ' Unload the UserForms from memory
    Unload ProtectSheetOptions
    Unload ProtectSheetPasswordConfirm
    Exit Sub


catch:
MsgBox FN_NAME, vbCritical, "Error"
Resume finally
    
End Sub


Private Sub ProtectWorksheet(ws As Worksheet)


'Protects individual worksheets based on the values of global variables set by the ProtectSheetOptions UserForm
Const FN_NAME As String = "ProtectWorksheet"


On Error GoTo catch
    
'### The ws.Protect... line is where the current version breaks when initialised with two or more sheets ###
    ws.Protect _
        Password:=PROTECT_SHEETS_PASSWORD, _
        DrawingObjects:=PROTECT_EDIT_OBJECTS, _
        Contents:=True, _
        Scenarios:=PROTECT_EDIT_SCENARIOS, _
        AllowFormattingCells:=PROTECT_FORMAT_CELLS, _
        AllowFormattingColumns:=PROTECT_FORMAT_COLS, _
        AllowFormattingRows:=PROTECT_FORMAT_ROWS, _
        AllowInsertingColumns:=PROTECT_INSERT_COLS, _
        AllowInsertingRows:=PROTECT_INSERT_ROWS, _
        AllowInsertingHyperlinks:=PROTECT_INSERT_LINKS, _
        AllowDeletingColumns:=PROTECT_DELETE_COLS, _
        AllowDeletingRows:=PROTECT_DELETE_ROWS, _
        AllowSorting:=PROTECT_SORT, _
        AllowFiltering:=PROTECT_FILTER, _
        AllowUsingPivotTables:=PROTECT_USE_PIVOTS
    
    If PROTECT_SELECT_LOCKED = False And PROTECT_SELECT_UNLOCKED = False Then
        ' If neither options are True, then no cells may be selected
        ws.EnableSelection = xlNoSelection
    ElseIf PROTECT_SELECT_LOCKED = False Then
        ' If '.._LOCKED' is False then '.._UNLOCKED' is FALSE by default
        ws.EnableSelection = xlUnlockedCells
    ElseIf PROTECT_SELECT_LOCKED = True And PROTECT_SELECT_UNLOCKED = True Then
        ws.EnableSelection = xlNoRestrictions
    Else
        ' No more options available, included as a fail-safe
        GoTo catch
    End If
    
finally:
    Exit Sub


catch:
MsgBox FN_NAME, vbCritical, "Error"
Resume finally


End Sub