Using Excel VBA to capture standard toolbar use

Question

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.

Answer 1

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