VBA 2003 Save|Change|Restore builtin menu OnAction

[Guest]

How can I save the OnAction property of the builtin menu items?

In Worksheet_Activate event handler I would like to change the OnAction
property of several builtin menu items and then in the Worksheet_Deactivate
event handler restore them to what they were before I changed them.

Thanks for your help
Jeff Higgins

 

[Guest]

Here is a solution I've come up with:

A class module named "EventClass" contains:

Public WithEvents App As Application
Public WithEvents CutMenuCommand As Office.CommandBarButton

Private Sub CutMenuCommand_Click(ByVal Ctrl As Office.CommandBarButton, _
CancelDefault As Boolean)
CancelDefault = True
Call Sheet1_OnCut
End Sub

--------------------------------------------------------------------------------

A standard module named "WorksheetFunctions" contains:

Public AppClass As New EventClass
Public CmdBars As CommandBar

Public Sub Init_Workbook()
Set AppClass.App = Application
Set CmdBars = AppClass.App.CommandBars("Worksheet Menu Bar")
Set AppClass.CutMenuCommand = CmdBars.Controls("&Edit").Controls("Cu&t")
End Sub

Public Sheet1_OnCut()
MsgBox ("Cut menu_item Clicked")
End Sub

----------------------------------------------------------------------------

"ThisWorkbook" module contains:

Private Sub Workbook_Activate()
If AppClass.CutMenuCommand Is Nothing Then
Set AppClass.CutMenuCommand =
CmdBars.Controls("&Edit").Controls("Cu&t")
End If
End Sub

Private Sub Workbook_Deactivate()
If Not AppClass.CutMenuCommand Is Nothing Then
Set AppClass.CutMenuCommand = Nothing
End If
End Sub

Private Sub Workbook_Open()
Call Init_Workbook
End Sub

--------------------------------------------------------------------------------

"Sheet1" module contains:

Private Sub Worksheet_Activate()
If AppClass.CutMenuCommand Is Nothing Then
Set AppClass.CutMenuCommand =
CmdBars.Controls("&Edit").Controls("Cu&t")
End If
End Sub

Private Sub Worksheet_Deactivate()
If Not AppClass.CutMenuCommand Is Nothing Then
Set AppClass.CutMenuCommand = Nothing
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Disable Edit|Cut / Edit|Paste operation in the sheet named "Sheet1"
'In case I've missed any toolbar/menu options that will cause
Cut/Copy/Paste
If AppClass.App.CutCopyMode = xlCut Or AppClass.App.CutCopyMode = xlCopy
Then
AppClass.App.CutCopyMode = False
AppClass.App.CellDragAndDrop = False
End If
End Sub