Repeating Menu item

[Stephen Giles]

Hi Gang,

I have a weird situation I can't get my head around.

I have created a macro that call a calendar control to make it easier to add
dates into my spreadsheet.

I also added the functionality to the right click context menu with the
following code (added to Personal.xls):


Private Sub Workbook_Open()

Dim NewControl As CommandBarControl
Application.OnKey "+^{C}", "ThisWorkbook.OpenCalendar"
Set NewControl = Application.CommandBars("Cell").Controls.Add

With NewControl
.Caption = "Insert Date"
.OnAction = "Module1.OpenCalendar"
.BeginGroup = True
End With
End Sub
It works fine, but every time I closed and re-open the worksheet a new
Insert data item is added to the menu (I currently have 14).

I tried adding:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set NewControl = Nothing
End Sub

but this did not seem to work either.

Any suggestions would be much appreciated.

Steve

 

[Ron de Bruin]

Hi Stephen

First run this one time to reset the Cell menu

Application.CommandBars("Cell").Reset

In your code delete the control first before you create it

On Error Resume Next
Application.CommandBars("Cell").Controls("Insert Date").Delete
On Error GoTo 0

See also
http://support.microsoft.com/default.aspx?scid=kb;en-us;830502&Product=xlw

 

[Stephen Giles]

That's the trick.

Thanks Ron!

Hi Stephen

First run this one time to reset the Cell menu

Application.CommandBars("Cell").Reset

In your code delete the control first before you create it

On Error Resume Next
Application.CommandBars("Cell").Controls("Insert Date").Delete
On Error GoTo 0

See also
http://support.microsoft.com/default.aspx?scid=kb;en-us;830502&Product=xlw