Right click menu problem
 

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!
--
EW - Analyst

Right click menu problem
 

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


"ewagz" wrote in message
...
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!
--
EW - Analyst

Right click menu problem
 

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

Charles

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


"ewagz" wrote in message
...
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!
--
EW - Analyst

Right click menu problem
 

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
--
EW - Analyst


"Bernie Deitrick" wrote:

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


"ewagz" wrote in message
...
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!
--
EW - Analyst

Right click menu problem
 

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" wrote in message
...
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
--
EW - Analyst

Right click menu problem
 

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


"Bernie Deitrick" wrote:

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" wrote in message
...
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
--
EW - Analyst