Excel中批量保护和撤销工作表

Excel中如果想对Sheet进行控制，防止其他人编辑、修改、复制等操作。需要为每个Sheet开启保护功能。详细做法可以参考这里。但是一次只能修改一个Sheet。如果有很多Sheet都需要设定保护功能的话，就会非常繁琐。这里提供了一个通过VBA方法，可以做到对一个Excel总的所有Sheet批量保护的功能。注意需要Excel开启宏功能。

Sub 批量保护()
Dim sh As Worksheet
Dim pwd As String
Dim repwd As String
Do
    pwd = InputBox("请输入保护密码,为空时不进行保护：")
    repwd = InputBox("再次输入保护密码：")
    If pwd = repwd Then
       For Each sh In Worksheets
       'Protect( Password , DrawingObjects , Contents , Scenarios , UserInterfaceOnly , AllowFormattingCells , AllowFormattingColumns , AllowFormattingRows , AllowInsertingColumns , AllowInsertingRows , AllowInsertingHyperlinks , AllowDeletingColumns , AllowDeletingRows , AllowSorting , AllowFiltering , AllowUsingPivotTables )
       sh.EnableSelection = xlNoSelection
       sh.Protect pwd, True, True, True, True, False, False, False, False, False, False, False, False, False, True, False
       Next
       Exit Sub
    End If
Loop
End Sub

Sub 批量解除()
Dim sh As Worksheet
Dim pwd As String
pwd = InputBox("请输入解除密码：")
For Each sh In Worksheets
    sh.Unprotect pwd
Next
End Sub

Sub 批量保护()

Dim sh As Worksheet

Dim pwd As String

Dim repwd As String

pwd = InputBox("请输入保护密码,为空时不进行保护：")

repwd = InputBox("再次输入保护密码：")

If pwd = repwd Then

For Each sh In Worksheets

'Protect( Password , DrawingObjects , Contents , Scenarios , UserInterfaceOnly , AllowFormattingCells , AllowFormattingColumns , AllowFormattingRows , AllowInsertingColumns , AllowInsertingRows , AllowInsertingHyperlinks , AllowDeletingColumns , AllowDeletingRows , AllowSorting , AllowFiltering , AllowUsingPivotTables )

sh.EnableSelection = xlNoSelection

sh.Protect pwd, True, True, True, True, False, False, False, False, False, False, False, False, False, True, False

Exit Sub

End If

Loop

End Sub

Sub 批量解除()

Dim sh As Worksheet

Dim pwd As String

pwd = InputBox("请输入解除密码：")

For Each sh In Worksheets

sh.Unprotect pwd

End Sub

Excel中批量保护和撤销工作表

文章评论