assign a macro to a coding

[azad092]

hi
dear all
good morning
I am using the following code to prevent the copy/cut and paste command by the user which is working smoothly.

I want to use this code with a macro....guide me how I can add a macro to enable this coding and another macro to disable this coding

Private Sub Workbook_Activate()
Application.CutCopyMode = False
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
MsgBox "You Are Not Allowed To Paste Anything"" " & vbNewLine & "For Futher Help Contact Admin", , "PES RESCUE 1122 MULTAN"
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

 

[dmt32]

Hi,
You can make your codes a common procedure that you pass argument to (on / off switch) that you can call from anywhere in your project

Place following code is a STANDARD module

Sub SetDragAndDrop(ByVal AllowDragAndDrop As Boolean, Optional ByVal ShowMsg As Boolean)

    With Application
       If AllowDragAndDrop Then .OnKey "^c" Else .OnKey "^c", ""
        .CellDragAndDrop = AllowDragAndDrop
        .CutCopyMode = False
    End With
    If ShowMsg Then MsgBox "You Are Not Allowed To Paste Anything"" " & vbNewLine & _
                            "For Futher Help Contact Admin", 64, "PES RESCUE 1122 MULTAN"
End Sub

I have included Optional argument to allow display of your msgbox if required - default is False (do not display)
Placing this code in a standard module, you can now call it as required from within your project.


You can now update your event codes in the Thisworkbook code page as follows

Private Sub Workbook_Activate()
    SetDragAndDrop AllowDragAndDrop:=False, ShowMsg:=True
End Sub
Private Sub Workbook_Deactivate()
    SetDragAndDrop AllowDragAndDrop:=True
End Sub
Private Sub Workbook_WindowActivate(ByVal Wn As Window)
    SetDragAndDrop AllowDragAndDrop:=False
End Sub
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
    SetDragAndDrop AllowDragAndDrop:=True
End Sub

Note: I have shown the Parameter names to make it clearer to you what action is being taken when calling the procedure but you can omit doing this & just pass the values to shorten the code if you prefer

example

SetDragAndDrop False, True

Hope Helpful

Dave

 

[Yongle]

1. Another way to write your code avoiding repitition (place in ThisWorkook code window) :

Private Sub Workbook_Activate()
    SwitchOn
End Sub
Private Sub Workbook_WindowActivate(ByVal Wn As Window)
    SwitchOn
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    SwitchOn
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    SwitchOn
End Sub

Private Sub Workbook_Deactivate()
    SwitchOff
End Sub
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
    SwitchOff
End Sub
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    SwitchOff
End Sub

Private Sub SwitchOn()
    Application.CutCopyMode = False
    Application.OnKey "^c", ""
    Application.CellDragAndDrop = False
End Sub
Private Sub SwitchOff()
    Application.CutCopyMode = False
    Application.OnKey "^c"
    Application.CellDragAndDrop = True
End Sub

.
.
2. This does not answer your question but may provide a different way for you to achieve what you want
- what EXACTLY do you want to be able to do within the workbook when the switch is turned off?
- it may be easier to write a macro to give you that ability

 

[azad092]

1. Another way to write your code avoiding repitition (place in ThisWorkook code window) :

Private Sub Workbook_Activate()
    SwitchOn
End Sub
Private Sub Workbook_WindowActivate(ByVal Wn As Window)
    SwitchOn
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    SwitchOn
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    SwitchOn
End Sub

Private Sub Workbook_Deactivate()
    SwitchOff
End Sub
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
    SwitchOff
End Sub
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    SwitchOff
End Sub

Private Sub SwitchOn()
    Application.CutCopyMode = False
    Application.OnKey "^c", ""
    Application.CellDragAndDrop = False
End Sub
Private Sub SwitchOff()
    Application.CutCopyMode = False
    Application.OnKey "^c"
    Application.CellDragAndDrop = True
End Sub

.
.
2. This does not answer your question but may provide a different way for you to achieve what you want
- what EXACTLY do you want to be able to do within the workbook when the switch is turned off?
- it may be easier to write a macro to give you that ability

thanks for your attention
but i mean that i want to use this coding with a macro/command button
so review it again and then guide me thanks

 

[Yongle]

I will if you answer my question

 

[azad092]

I will if you answer my question

yes exactly I want to use macro to prevent the copy/cut and paste command ....the above mentioned code works very well but whenever i want to enable the copy/cut and paste command I have to open the VBA explorer and have to stop or delete the coding... So I also want that instead of doing this I just run a macro to enable/disable these commands...... Dear Member I have explained you and i hope you got it... thanks again for help

 

[Yongle]

This is why I asked what you want to do ....

Cell Drag&Drop is not a problem
Use a toggle button to toggle between Application.CellDragAndDrop = True / Application.CellDragAndDrop = False

Copy, Cut and Paste is different
- the marching ants cause a problem
- they dissappear between copy(cut) and paste if interrupted by the event macros
- which prevents paste from working

Workaround
The simplest way is to write macros to allow you to do what you want

Example to copy a range - place this in a module and call it from a button

Sub CopyRange()
    Dim copyRng, pasteCell As Range
    Set copyRng = Application.InputBox("Select range to copy and click OK", "COPY", , , , , , 8)
    Set pasteCell = Application.InputBox("Select paste cell & click OK", "PASTE", , , , , , 8)
    copyRng.Copy pasteCell
End Sub

Macro requires tailoring to allow paste values etc ...

 

[azad092]

This is why I asked what you want to do ....

Cell Drag&Drop is not a problem
Use a toggle button to toggle between Application.CellDragAndDrop = True / Application.CellDragAndDrop = False

Copy, Cut and Paste is different
- the marching ants cause a problem
- they dissappear between copy(cut) and paste if interrupted by the event macros
- which prevents paste from working

View attachment 12255

Workaround
The simplest way is to write macros to allow you to do what you want

Example to copy a range - place this in a module and call it from a button

Sub CopyRange()
    Dim copyRng, pasteCell As Range
    Set copyRng = Application.InputBox("Select range to copy and click OK", "COPY", , , , , , 8)
    Set pasteCell = Application.InputBox("Select paste cell & click OK", "PASTE", , , , , , 8)
    copyRng.Copy pasteCell
End Sub

Macro requires tailoring to allow paste values etc ...

bundle of thanks dear