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:
- Open Excel then open the Visual Basic Editor (Alt+F11).
- Open the Immediate Window (Ctrl+G) and in it type the following
line of code:
- 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:
- 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
On Error GoTo 0
- On the File menu choose Save Calendar.xla.
- Close the Visual Basic Editor and restart Excel.
The modified Workbook_Open procedure should look like this: