0

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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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