I am trying to trigger a right click menu within an XLA file. My code in my
class module is as follows below. My class module is named "EventClass". I
can't seem to get the menu to trigger of the right click. The subs that get
called are in a regular module. And I currently do not have any code at all
in my ThisWorkbook module.

Thanks

Option Explicit
Public WithEvents App As Excel.Application
Public AppClass As EventClass

Private Sub Workbook_Open()
Set AppClass = New EventClass
Set AppClass.App = Excel.Application
End Sub

Private Sub App_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As
Range, Cancel As Boolean)
Call DeleteCustomMenu 'remove possible duplicates
Call BuildCustomMenu 'build new menu
End Sub

Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
Call DeleteCustomMenu
End Sub

You need to separate the code.

This code goes in This Workbook (note Private AppClass not Public)

Private AppClass As EventClass

Private Sub Workbook_Open()
Set AppClass = New EventClass
Set AppClass.App = Excel.Application
End Sub

Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
Call DeleteCustomMenu
End Sub

and this in the class module

Option Explicit

Public WithEvents App As Excel.Application

Private Sub App_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As
Range, Cancel As Boolean)
Call DeleteCustomMenu 'remove possible duplicates
Call BuildCustomMenu 'build new menu
End Sub



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ExcelMonkey" wrote in message
...
I am trying to trigger a right click menu within an XLA file. My code in
my
class module is as follows below. My class module is named "EventClass".
I
can't seem to get the menu to trigger of the right click. The subs that
get
called are in a regular module. And I currently do not have any code at
all
in my ThisWorkbook module.

Thanks

Option Explicit
Public WithEvents App As Excel.Application
Public AppClass As EventClass

Private Sub Workbook_Open()
Set AppClass = New EventClass
Set AppClass.App = Excel.Application
End Sub

Private Sub App_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As
Range, Cancel As Boolean)
Call DeleteCustomMenu 'remove possible duplicates
Call BuildCustomMenu 'build new menu
End Sub

Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As
Boolean)
Call DeleteCustomMenu
End Sub

"ExcelMonkey" wrote in message
...
I think I am going to name my kid after you (due in April!). This problem
has been driving me nuts! Thanks so much. Two three questions:

1) If I call the build/delete subs in the Open/Close events as you suggest
does the open event reside in the class module as App_WorkbookOpen?


No. because that means the event would trigger when an y workbook is opened
after that one, when you want it to apply to when that workbook is opened.
So just add it to a normal Workbook_Open of ThisWorkbook in the workbook
that will create this functionality.


2) Does doing this mean I have to change the new IF stmt you just put in
the
Delete sub?


No not at all. You currently get it when you second-time right-click,
beacuse it tries to delete the controls before re-creating them. If you
change it to a 'do once' method, you won't get the problem so quickly with
the old code, but you will still get it when you close the workbook. So the
change is still valid.


3) As the menu items grow, I can see the IF getting quite large in the
Delete sub. If I plan on adding to these, how do I minimize the code in
this
module.

I would just do away with the If and wrap it in an On Error

On Error resume Next
With Application.CommandBars("Cell")
.Controls("New Edit...").Delete
.Controls("Validation Check...").Delete Then
ctrl.Delete
On Error Goto 0

That way, if it exists, it is deleted nicely, if it doesn't, the Error
Resume makes sure that it doesn't fail. Adding more items will be simpler
then, and much more readable.

BTW, if you are going to add more items, you don't need to define them all
as popups and then hang single buttons off them. You usually have popups as
a grouping menu item. So you could (better) use

Sub BuildCustomMenu()
Dim ctrl As CommandBarControl
Dim btn As CommandBarControl
Dim i As Integer

'add first 'popup' control to the cell commandbar (menu)
With Application.CommandBars("Cell")
Set btn = .Controls.Add (Type:=msoControlButton, Befo=5)
btn.BeginGroup = True
btn.Caption = "Copy Cell Address" 'give them a name
btn.OnAction = "CopyRangeAddress" 'the routine called by the control

'add second 'popup' control to the cell commandbar (menu)
Set btn = .Controls.Add (Type:=msoControlButton, Befo=6)
btn.Caption = "List Correct?" 'give them a name
btn.OnAction = "ValidationCheck" 'the routine called by the control
End With

Set btn = Nothing

End Sub

or even

Sub BuildCustomMenu()
Dim ctrl As CommandBarControl
Dim btn As CommandBarControl
Dim i As Integer

'add first 'popup' control to the cell commandbar (menu)
With Application.CommandBars("Cell")
Set ctrl = .Controls.Add (Type:=msoControlPopup, Befo=5)
ctrl.BeginGroup = True
ctrl.Caption = "Menu 1"
with ctrl
Set btn = .Controls.Add(Type:=msoControlButton)
btn.Caption = "Copy Cell Address" 'give them a name
btn.OnAction = "CopyRangeAddress" 'the routine called by the
control

Set btn = .Controls.Add (Type:=msoControlButton)
btn.Caption = "List Correct?" 'give them a name
btn.OnAction = "ValidationCheck" 'the routine called by the
control
End With
End With

Set btn = Nothing

End Sub

of course the delete code would need changing accordingly