• Back to the Mailbag
• A Pop-up Calendar for Excel

My Excel Calendar Keeps Making Extra "Insert Date" Entries

My tutorial A Pop-up Calendar for Excel shows how to build a pop-up calendar that could be saved as an Excel Add-In, or as a macro in your Personal.xls file. It includes code to place an Insert Date entry on the shortcut menu which appears when you right-click on a cell. This code runs when Excel opens, and there is corresponding code to remove the entry when Excel closes. However, in certain circumstances the entry is not removed as it should be, and multiple copies of Insert Date appear on the menu. Here's how to fix the problem.

Remove Extra Menu Items

If you see more than one Insert Date entry in the shortcut menu when you right-click on a cell, they can be removed easily as follows:

1. Open Excel then open the Visual Basic Editor (Alt+F11).
2. Open the Immediate Window (Ctrl+G) and in it type the following line of code:
   
   Application.CommandBars("Cell").Controls("Insert Date").Delete
3. Press the Enter key.

This will remove one of the Insert Date entries. If you need to remove more, just place your cursor into the code line that you just typed (there's no need to retype it) and press Enter again. Do this as many times as necessary to remove all but one of the entries.

Modify the Code

In the Project Explorer (in the upper left corner of the Visual Basic Editor) find the entry for VBAProject (Calendar.xla) (or if you created the calendar in another workbook such as Personal.xls find VBAProject (Personal.xls)). If necessary click the plus sign [+] next to the entry to expand it, then click the plus sign next to Microsoft Excel Objects and double-click on the ThisWorkbook entry to open its code window.

The ThisWorkbook code module contains two procedures. One of them needs to be edited. Here's what to do:

1. Locate the Workbook_Open procedure and place your cursor at the beginning of the line starting Set NewControl... and press the Enter key to create an empty line above it. Move into the empty line and type the following three lines of code:
   
   On Error Resume Next
   Application.CommandBars("Cell").Controls("Insert Date").Delete
   On Error GoTo 0
2. On the File menu choose Save Calendar.xla.
3. Close the Visual Basic Editor and restart Excel.

The modified Workbook_Open procedure should look like this:

©2004 Martin Green. All rights reserved. martin@fontstuff.com