Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

1 Answer

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.