Repeating Menu item
 

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

Repeating Menu item
 

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...02&Product=xlw


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Stephen Giles" wrote in message ...
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

Repeating Menu item
 

That's the trick.

Thanks Ron!

"Ron de Bruin" wrote:

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...02&Product=xlw


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Stephen Giles" wrote in message ...
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