Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am looking for a way to capture a click on a standard toolbar tool in Excel with VBA. The primary need is to capture any Copy, Cut, and Paste request by the user. I can capture the hot keys for these functions, but I can't find a way to capture the same functions when the user clicks on the icon on the standard toolbar or when the user uses the menu to select the function.

share|improve this question

Here is an undo example from John Walkenbalk http://www.j-walk.com/ss/excel/tips/tip23.htm

I've also used a macro (Called by the worksheet_change event) to force a user to 'paste special.' It should give you a good starting point.

Sub pastefix()
    Dim UndoString As String
    Dim srce As Range

On Error GoTo err_handler

    UndoString = _
        Application.CommandBars("Standard").Controls("&Undo").List(1)

    If Left(UndoString, 5) <> "Paste" And UndoString <> "Auto Fill" Then
        Exit Sub
        End If

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Undo

    If UndoString = "Auto Fill" Then
        Set srce = Selection
        srce.Copy
        ActiveCell.PasteSpecial _
            Paste:=xlPasteValues, _
            Operation:=xlNone, _
            SkipBlanks:=False, _
            Transpose:=False
        Union(ActiveCell, srce).Select
    Else
        ActiveCell.PasteSpecial _
            Paste:=xlPasteValues, _
            Operation:=xlNone, _
            SkipBlanks:=False, _
            Transpose:=False
    End If

    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    Application.EnableEvents = True

    Exit Sub

err_handler:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.CutCopyMode = False
End Sub
share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .