Repeating Menu item

S

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
 

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