Excel Activecell Right Click Menu Issue

[MikeSawt]

I have written the following code in personal.xls to add a pop up calendar

First to add the menu item.
___
Private Sub Workbook_Open()
Dim NewControl As CommandBarControl
Set NewControl = Application.CommandBars("Cell&quot.Controls.Add
With NewControl
.Caption = "Insert Date"
.OnAction = "Module1.opencalendar1"
.BeginGroup = True
End With

End Sub
_____

Then to remove the item before closing.
___
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("Cell&quot.Controls("Insert Date&quot.Delete
End Sub
__

My problem is the "Insert Date" menu items appears multiple times. It does not recognize this code
”Application.CommandBars("Cell&quot.Controls("Insert Date&quot.Delete”

If I use the immediate window and type
“Application.CommandBars("Cell&quot.Controls("Insert Date&quot.Delete”
The menu item is removed

How do I get excel to recognize the "delete code" before close without having to use the immediate window?

Mike

 

[combo]

In excel XP I got syntax error, corrected after adding Type:
[tt]Set NewControl = Application.CommandBars("Cell&quot.Controls.Add(Type:=msoControlButton)[/tt]

The Workbook_BeforeClose procedure works well, so try to remove 'On Error Resume Next' to get info about error.

combo

 

[MikeSawt]

I removed the 'On Error Resume Next' line and I did not recieve an error message.

However the menu items is not removed, and appears multiple times.

 

[combo]

In my test, when there is no "Insert Date" control, an error 'Invalid procedure call or argument' is reported. What happens when you remove manually control and close excel? Have you got somewhere EnableEvents=False and not resetted to True later?
I would test those events with:
[tt]Private Sub Workbook_Open()
Dim NewControl As CommandBarControl
Msgbox "Control added"
....

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Msgbox "Control deleted"
Application.CommandBars("Cell&quot.Controls("Insert Date&quot.Delete
End Sub[/tt]

You will know when events fire and see their effect.

combo

 

[MikeSawt]

I don’t get that error because I have created a form called “frmCalendar”. I have a procedure to open the calendar called openCalendar1
____
Sub openCalendar1()
frmCalendar.Show
End Sub
___
The only Enable property I could find was in the frmCalendar and it is set to true, the default setting. I have not changed this.

I tried your suggestion, with the message boxes. When I open excel, the message box appears, but nothing appears when I close excel.

When I remove the control manually, the control is removed but I do not see the message box.

If I put the delete code in the VBAProjects(Book1), Thisworkbook , On closing I get the delete control message. But the next time I open excel, the code is not in the VBAProjects(Book1)ThisWorkbook folder so it does not fire.

Am I putting the code in the right location? I have it in the VBAProjects(Personal.xls) ThisWorkbook

Mike

 

[combo]

Personal.xls workbook is one of ways to get silently opened workbook with access to its code. Guess that you have all (form, module1 and open/beforeclose procedures) in it.
Each time you start excel it is opened and its Workbook_Open procedure fires and adds a button. You get the 'created' message. Seems that the Workbook_BeforeClose procedure is in another workbook; should be in VBAProjects(Personal.xls) ThisWorkbook, together with Workbook_Open. (To get this module, double click it in the project explorer.)
As for the EnableEvents property, this can be set only in code (userform module, sheets' modules or standard), for Application object. It looks like from your post, that it is not the case, rather Workbook_BeforeClose procedure destroying control is in a workbook other than Presonal.xls

combo

 

[MikeSawt]

The code below is all in the same workbook:
VBAProject(Personal.xls)
-> Microsoft Excel Objects
-> ThisWorkbook

For some reason it is not recognizing the Delete Code?????

Private Sub Workbook_Open()
Dim NewControl As CommandBarControl
MsgBox "Control added"
Set NewControl = Application.CommandBars("Cell&quot.Controls.Add
With NewControl
.Caption = "Insert Date"
.OnAction = "Module1.opencalendar1"
.BeginGroup = True
End With
End Sub
___
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
MsgBox "Control deleted"
Application.CommandBars("Cell&quot.Controls("Insert Date&quot.Delete

End Sub

 

[combo]

I've just made quick test with Personal.xls. Seems that it fires 'open' event, but not 'beforeclose'!!!. This is why the button is not removed.
Plan B: create an add-in. Drag Userform and Module1 to a new workbook, copy code between ThisWorkbook modules and save the workbook as an add-in (last item on the list) and close workbook. Install add-in (tools>addins..).
Both events (open and beforeclose) fire when you open and close excel or install/uninstall add-in, so there should be no problem with deleting the menu item.

combo

 

[MikeSawt]

I found my problem!!!!
I did not have the "add context menu item module" check box checked in the addins menu from the tools menu.

It works fine now.
Combo, Thanks for the help

Mike