将受保护的工作表宏应用于工作簿中的所有工作表

I have a macro that opens automatically and applies a special sheet protection which allows for grouping and some editing for a specific sheet, Program Data. The macro I have is this:

我有一个自动打开的宏,并应用一个特殊的表单保护,允许对特定表单程序数据进行分组和编辑。我的宏是这样的:

Private Sub Workbook_Open()
  With Worksheets("Program Data")
   .EnableOutlining = True
   .Protect UserInterfaceOnly:=True, AllowFiltering:=True, AllowFormattingColumns:=True,    
    AllowInsertingRows:=True
  End With
End Sub

Right now this runs as soon as you open the file, but it only applies to Program Data. I don't know how to change the with statement to accommodate the entire workbook without naming the sheets one-by-one. But there are very many sheets, so that's not a good option. How do I do apply this for all sheets in the file?

现在,只要您打开文件,它就会立即运行,但它仅适用于程序数据。我不知道如何更改with语句以适应整个工作簿,而无需逐个命名工作表。但是有很多床单,所以这不是一个好选择。如何将此应用于文件中的所有工作表?

3 个解决方案

#1

You want a For loop that targets all the sheets. Basically...

您需要一个针对所有工作表的For循环。基本上...

Private Sub Workbook_Open()
  Dim WS As Worksheet
  For Each WS in ThisWorkbook.Worksheets
    With WS
    .EnableOutlining = True
    .Protect UserInterfaceOnly:=True, AllowFiltering:=True, AllowFormattingColumns:=True,    
    AllowInsertingRows:=True
    End With
  Next WS
End Sub

Let us know if this helps.

如果这有帮助,请告诉我们。

#2

You can loop through the worksheet objects like this (UNTESTED).

您可以像这样遍历工作表对象(UNTESTED)。

Private Sub Workbook_Open()
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        With ws
            .EnableOutlining = True
            .Protect UserInterfaceOnly:=True, AllowFiltering:=True, _
            AllowFormattingColumns:=True, AllowInsertingRows:=True
        End With
    Next
End Sub

#3

try this

For i = 1 To Worksheets.Count - 1
    With Worksheets(i)
        .EnableOutlining = True
        .Protect UserInterfaceOnly:=True, AllowFiltering:=True,  AllowFormattingColumns:=True,     AllowInsertingRows:=True
    End With
Next

#1