我有 7 个宏可以阻止用户在工作簿中复制和粘贴。
但是,有时我希望能够复制和粘贴。有没有办法用另一个宏禁用这些宏?
我只能找到有关如何禁用所有宏的信息,而这似乎无法通过另一个宏本身实现。
我还希望能够重新打开宏,并计划在工作簿关闭时运行该宏,以便下一个用户默认无法复制和粘贴。
以下是我用来防止复制和粘贴的宏。
Private Sub Workbook_Activate()
Application.CutCopyMode = False
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
End Sub
Private Sub Workbook_Deactivate()
Application.CellDragAndDrop = True
Application.OnKey "^c"
Application.CutCopyMode = False
End Sub
Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Application.CutCopyMode = False
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
End Sub
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Application.CellDragAndDrop = True
Application.OnKey "^c"
Application.CutCopyMode = False
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.CutCopyMode = False
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
Application.CutCopyMode = False
End Sub
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Application.CutCopyMode = False
End Sub
答案1
不幸的是,在 VBA 中,在宏运行之间传输状态/信息并不容易,因为在宏运行之间没有变量可以保留。你有两个选择:
从另一个宏更改代码
进一步阅读:https://stackoverflow.com/questions/14811343/在excel-vba中是否可以更改其他模块中的源代码将状态信息存储在单元格中。
您可以使用电子表格右下角的单元格,因为用户不经常使用它。
您可以修改代码,例如
Private Sub Workbook_Activate()
If Sheets(1).Range("XFD1048576") = "paused" Then
Exit Sub
End If
Application.CutCopyMode = False
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
End Sub