Excel

Custom Event That Occurs Before Copy

Ease of Use

Intermediate

Version tested with

2002 

Submitted by:

Jacob Hilderbrand

Description:

This macro demonstrates how to create a custom event. In this case a "before copy" event. 

Discussion:

You may want to change what the copy function does, or you just need to know when something is copied. This macro changes the copy buttons from the command bars to actually refer to a custom macro, that can then do anything you want. This macro is mostly for demonstration purposes, since bypassing the copy event entirely would mean intercepting all methods of copying. 

Code:

instructions for use 

			
Option Explicit 
 
Sub ChangeCopy() 
     
    Dim CmdBar          As CommandBar 
    Dim CmdCtl          As CommandBarControl 
     
    For Each CmdBar In CommandBars 
        Set CmdCtl = CmdBar.FindControl(ID:=19, recursive:=True) 
        If Not CmdCtl Is Nothing Then CmdCtl.OnAction = "CustomCopy" 
    Next CmdBar 
     
    Set CmdBar = Nothing 
    Set CmdCtl = Nothing 
     
End Sub 
 
Sub ResetCopy() 
     
    Dim CmdBar          As CommandBar 
    Dim CmdCtl          As CommandBarControl 
     
    For Each CmdBar In CommandBars 
        Set CmdCtl = CmdBar.FindControl(ID:=19, recursive:=True) 
        If Not CmdCtl Is Nothing Then CmdCtl.OnAction = "" 
    Next CmdBar 
     
    Set CmdBar = Nothing 
    Set CmdCtl = Nothing 
     
End Sub 
 
Sub CustomCopy() 
     
    MsgBox "You tried to copy the data, that request has been canceled", vbCritical, "Copy Canceled" 
     
End Sub

How to use:

  1. Open Excel.
  2. Alt + F11 to open the VBE.
  3. Insert | Module.
  4. Paste the code there.
  5. Modify the macro CustomCopy to do whatever you want, currently it is setup to display a message box.
  6. Close the VBE (Alt + Q or press the X in the top right corner).
  7. If you want set a keyboard shortcut (Ctrl + c) to the CustomCopy macro to disable the default copy shortcut.
  8. To set the keyboard shortcut: Tools | Macro | Macros...
  9. Select CustomCopy and press Options.
  10. Input the keyboard shortcut and press Ok.
 

Test the code:

  1. Tools | Macro | Macros...
  2. Select ChangeCopy and press Run to change the copy buttons.
  3. Select ResetCopy and press Run to reset the copy buttons back to normal.
 

Sample File:

CustomCopy.ZIP 8KB 

Approved by mdmackillop


This entry has been viewed 114 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express