"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