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.
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