隔离Excel脚本来运行特定的工作表？
隔离Excel脚本来运行特定的工作表？
提问于 2015-10-07 13:00:19
- 我有一个Excel电子表格，其中包含7个工作表。
- 每当保存文档时，我需要只将下面的脚本应用于部分工作表 (Sheet6 & Sheet7)。
我花了几个小时尝试不同的修改，其中必须是根本不起作用。VBA调试器不会抛出任何错误，而且当我测试脚本时，它似乎从未运行过。
当我从任何工作表选项卡中保存文档时，如何修改下面的脚本以针对特定的工作表运行？
谢谢
VBA -锁定单元及保存保护表
下面的脚本将锁定包含值的单元格，然后在保存之前密码保护工作表。
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
On Error Resume Next
Dim Cell As Range
With ActiveSheet
.Unprotect Password:=""
.Cells.Locked = False
For Each Cell In Application.ActiveSheet.UsedRange
If Cell.Value = "" Then
Cell.Locked = False
Else
Cell.Locked = True
End If
Next Cell
.Protect Password:=""
'Protect with blank password, you can change it
End With
Exit Sub
End Sub脚本源
回答 3
Stack Overflow用户
回答已采纳
发布于 2015-10-07 13:15:18
更改ActiveSheet并使用如下所示的For Each循环：
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
On Error Resume Next
Dim Cell As Range
For Each sh In Array("Sheet1", "AnotherSheet", "OtherSheet")
With Sheets(sh)
.Unprotect Password:=""
.Cells.Locked = False
For Each Cell In Application.ActiveSheet.UsedRange
If Cell.Value = "" Then
Cell.Locked = False
Else
Cell.Locked = True
End If
Next
.Protect Password:=""
End With
Next
End SubStack Overflow用户
发布于 2015-10-07 13:21:17
这将对您有所帮助(您将收到消息，让您知道什么时候是在活动中，什么时候开始和结束)：
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim Cell As Range
MsgBox "Event Workbook_BeforeSave Launched", vbInformation + vbOKOnly, "Started"
On Error GoTo ErrHandler
ReTry:
With Sheet6
.Unprotect Password:=""
.Cells.Locked = False
For Each Cell In .UsedRange
If Cell.Value = "" Then
Cell.Locked = False
Else
Cell.Locked = True
End If
Next Cell
.Protect Password:=""
'Protect with blank password, you can change it
End With
With Sheet7
.Unprotect Password:=""
.Cells.Locked = False
For Each Cell In .UsedRange
If Cell.Value = "" Then
Cell.Locked = False
Else
Cell.Locked = True
End If
Next Cell
.Protect Password:=""
'Protect with blank password, you can change it
End With
MsgBox "Event Workbook_BeforeSave Over", vbInformation + vbOKOnly, "Finished"
Exit Sub
ErrHandler:
MsgBox "Error " & Err.Number & " :" & vbCrLf & _
Err.Description
Resume ReTry
End SubStack Overflow用户
发布于 2015-10-08 02:51:03
代码可以显着地缩短(运行时)
- 使用
SpecialCells而不是循环遍历每个单元 - 避免将空白单元格设置为被锁定两次(与第一点相比较小)。
已更新
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
For Each sh In Array("Sheet1", "AnotherSheet", "OtherSheet")
With Sheets(sh)
.Unprotect
.Cells.Locked = True
On Error Resume Next
.Cells.SpecialCells(xlBlanks).Locked = False
On Error GoTo 0
.Protect
End With
Next
End Sub页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/32992912
复制相关文章
相似问题
腾讯云开发者
