Right click menu problem

[ewagz]

I recently added the following code to a spreadsheet to add a right click
menu to run one of my forms/macros. The problem that I'm having is that it's
adding this to every spreadsheet and also adds each time so there are
multiple instances of the same right click menu item. I need some help
please!

Sub workbook_open()


Dim NewControl As CommandBarControl

Set NewControl = Application.CommandBars("Cell").Controls.Add
With NewControl
.Caption = "Add reservation"
.OnAction = "Module3.Cust_UF"
.BeginGroup = True
End With

End Sub


Any help would be appreciated!

 

[Bernie Deitrick]

Sub workbook_open()

Dim NewControl As CommandBarControl

On Error Resume Next
Application.CommandBars("Cell").Controls.("Add reservation").Delete


Set NewControl = Application.CommandBars("Cell").Controls.Add
With NewControl
.Caption = "Add reservation"
.OnAction = "Module3.Cust_UF"
.BeginGroup = True
End With

End Sub

Sub workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("Cell").Controls.("Add reservation").Delete

End Sub

-
HTH,
Bernie
MS Excel MVP

 

[Die_Another_Day]

In addition to this, you could place Bernie's code in the Worksheet
Activate/DeActivate events so It only works for one worksheet.

Charles

 

[ewagz]

I seem to be having difficulties using the code and VBA is high lighting the
following code line:

Application.CommandBars("Cell").Controls.("Add reservation").Delete

I am also receiving the following error message when trying to run the macro:

Compile Error: Ambigous name detected: workbook_beforeclose

I'm using MS Excel 2000

 

[Bernie Deitrick]

EW,

I mistakenly left in an extra .

Application.CommandBars("Cell").Controls.("Add reservation").Delete

should be

Application.CommandBars("Cell").Controls("Add reservation").Delete

If you already have a workbook_beforeclose event, then you need to modify the existing procedure
rather than add a second procedure of the same name.

HTH,
Bernie
MS Excel MVP

 

[ewagz]

Thanks again, that works great!
--
EW - Analyst

EW,

I mistakenly left in an extra .

Application.CommandBars("Cell").Controls.("Add reservation").Delete

should be

Application.CommandBars("Cell").Controls("Add reservation").Delete

If you already have a workbook_beforeclose event, then you need to modify the existing procedure
rather than add a second procedure of the same name.

HTH,
Bernie
MS Excel MVP