Right click menu problem

E

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!
 
B

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
 
D

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
 
E

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
 
B

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
 
E

ewagz

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


Bernie Deitrick said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top