VBA Password protecting selected multiple sheets

[Nyanko]

Hi,

I'm trying to write a macro to help me manage protecting the excel files I work with. I want to be able to select (group) a selection of sheets then press a button to password protect only those sheets then ungroup them.

I will be using it as a personal macro rather than tied to a specific file so I can't assign named sheets to an array etc...

I know that because the sheets are grouped it's causing the protect routine to fail. Any suggestions for how to get round this ?

For Each sht In ActiveWindow.SelectedSheets
    sht.Protect Password:=strPassword
  Next sht

Can I assign the selected (grouped) sheets to an array and then loop through to protect ?
How do I change the "NameSheet","NamedSheet2" to only the selected sheet names ?

Set group = Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
   For Each sht In group
     sht.Activate
     ActiveSheet.Protect "Password"
Next sht

Any advise would be welcome

 

[FormR]

Can I assign the selected (grouped) sheets to an array and then loop through to protect ?

Hi, here's one way maybe:

Sub ProtectSheets()
Dim Shts() As Long, i As Long
ReDim Shts(1 To ActiveWindow.SelectedSheets.Count)
For i = 1 To UBound(Shts)
    Shts(i) = ActiveWindow.SelectedSheets(i).Index
Next i
ActiveWindow.SelectedSheets(1).Select
For i = 1 To UBound(Shts)
    Sheets(Shts(i)).Protect "Password"
Next i
End Sub

 

[Nyanko]

That is perfect !

I've never worked with Bound values before. I guess that's next on my research.

Thank you so much for this

 

[Nyanko]

Hi, here's one way maybe:

Sub ProtectSheets()
Dim Shts() As Long, i As Long
ReDim Shts(1 To ActiveWindow.SelectedSheets.Count)
For i = 1 To UBound(Shts)
    Shts(i) = ActiveWindow.SelectedSheets(i).Index
Next i
ActiveWindow.SelectedSheets(1).Select
For i = 1 To UBound(Shts)
    Sheets(Shts(i)).Protect "Password"
Next i
End Sub

Now ... I may be pushing it.
But can I take the names of the sheets that were protected and display in a message box at the the end : "The following sheets were protected : SheetName, SheetName2 etc..."

 

[FormR]

Hi, give this a try:

Sub ProtectSheets()
Dim Shts() As String, i As Long
ReDim Shts(1 To ActiveWindow.SelectedSheets.Count)
For i = 1 To UBound(Shts)
    Shts(i) = ActiveWindow.SelectedSheets(i).Name
Next i
ActiveWindow.SelectedSheets(1).Select
For i = 1 To UBound(Shts)
    Sheets(Shts(i)).Protect "Password"
Next i
MsgBox "The following sheets were protected: " & Join(Shts, ", ")
End Sub

 

[Nyanko]

That's beautiful !

I'm going to enjoy working out how it's done next

Thank you so much

 

[mattxu2006]

I also found this very helpful. Thank you.