Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Right click menu | Excel Programming | |||
Add menu item to right-click menu | Excel Programming | |||
Right click menu | Excel Programming | |||
Adding a menu item right click menu when clicking on a single. | Excel Programming | |||
Adding menu to the mouse right click pop-up menu | Excel Programming |