Jtdylktuy

I have 7 macros that prevent a user from copying and pasting in a workbook.

However, now and then I want to be able to copy and paste. Is there a way to disable these macros with another macro ?

I can only find information on how to disable all macros, and this does not seem achievable with another macro itself.

I would also want to be able to turn the macros back on, and plan to have this macro run whenever the workbook is closed, so that the next user, by default cannot copy and paste.

Below are the macros I use to prevent copying and pasting.

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

Unfortunately transferring status / information between run of macros is not easy in VBA as no variables can persist between run of macros. You have two options:

1. change the code from another macro
   
   Further reading: https://stackoverflow.com/questions/14811343/is-it-possible-in-excel-vba-to-change-the-source-code-of-module-in-another-modul




2. store the status information in a cell.
   
   You can use e.g. right bottom corner cell of a spreadsheet as that is not regularly used by users.
   
   You can modify your code e.g.

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