How Can I Make my Excel Calendar Appear Automatically When the User Enters a
My tutorial A Pop-up Calendar
for Excel shows how to build a pop-up calendar that can be saved as an
Excel Add-In, or as a macro in an Excel workbook such as
Personal.xls. The project includes code to make the calendar appear when the
user right-clicks on a cell and chooses Insert Date from the context
menu. But there are other ways to make the calendar appear, and one is to have
it appear by itself when a specific cell is selected.
About Worksheet Events
The pop-up calendar code includes a macro called OpenCalendar which
displays the calendar's UserForm. You can make use of one of the various
worksheet events to run a procedure which "calls" the OpenCalendar macro.
A "worksheet event" occurs when certain things happen on a worksheet. Several
are described below. The term "call" is used to describe the process of one macro being run
by another. There are several worksheet events which could be used to make the
calendar appear without the user having to ask for it specifically. All you have to do is
choose which one suits you best...
The "Worksheet_Selection Change" Event
As its name suggests this event fires whenever a different cell is selected.
It applies to all the usual methods of user selection including mouse-click, tab
key, enter key and arrow keys. You can use an If Statement to specify the cell
to which you want the procedure to apply. In this example the calendar will
appear automatically when the user enters cell B5:
If you want the procedure to apply to more than one cell you can add a
condition to the If Statement, for example:
A Case Statement is less clumsy when applying the procedure to a long list of
When the calendar appears the user can choose a date for entry into the cell,
or close the calendar without entering a date by pressing the Escape key
on their keyboard (the code enabling this feature was included in the calendar
The "Worksheet_BeforeDoubleClick" Event
This event fires when the user double-clicks on a cell. The Before
part of its name means that, although it is triggered by a double-click, it
happens before the double-click is processed by the program. Double-clicking a
cell in Excel normally places the cursor into that cell, putting it into "edit"
mode. This event offers the facility to cancel that effect of the double-click
whilst still allowing the OpenCalendar macro to be called. For example:
As a result, the calendar opens allowing the user to choose a date for entry
into the cell (or not as they choose) without putting the cell into edit mode.
As before, this code can be adapted to specify more than one cell.
The "Worksheet_BeforeRightClick" Event
When the user right-clicks on a cell they normally see the cell's "context"
menu. This procedure can be used to trap the right-click event and cancel it,
preventing the context menu from being displayed. The calendar is made to appear
Again, this code can be adapted to specify more than one cell.
Where Does the Code Go?
These events are specific to a particular worksheet so the code should go in
that worksheet's own code module. Locate the workbook in the Project Explorer
window of the Visual Basic Editor and double-click on the name of the worksheet
to open its code module. Use the drop-down lists at the top of the code window
to select the appropriate event for your code...
Setting a Reference to the Calendar File
Where is Your Pop-up Calendar Code?
If your Pop-up Calendar UserForm and its associated code is located in the
same workbook as the worksheet to which you are applying the worksheet event procedure, then it
will work perfectly without any further action. But if the calendar is located
elsewhere, such as in an Excel Add-In or in Personal.xls then you
have to "set a reference" to that file so that your code can recognise and find
the the OpenCalendar macro. This always applies when you want the code
in one workbook to call a macro from another workbook.
How to Set the Reference
Setting a reference to another workbook or Add-in file is a simple process,
but there is something else you will probably have to do first. You might have
noticed that the Visual Basic Editor refers to each file first by a project
name, then by its filename, for example: VBAProject (PERSONAL.XLS). By
default each file is assigned the project name VBAProject and normally
you don't have any need to change that.
But when you set a reference to a file you refer to it by its project name,
and if there are more than one with the same name this causes a conflict. So,
the first thing to do is to give a unique project name to the file to which
you want to set a reference. Here's how to do it:
- In the Project Explorer window of the Visual Basic Editor right-click on
the name of the file and choose VBAProject Properties...
- On the General tab of the dialog box replace the Project Name
VBAProject with something more meaningful and click OK. The
result is immediately reflected in the Project Explorer window:
||> > >
- Select the item you have just changed in the Project Explorer and choose
File > Save Filename (where Filename is the name of the
file whose project name you just changed).
Now you can set a reference to the file. First, in the Project Explorer,
click on the name of the file into which you have written your Worksheet Event
code. Choose Tools > References and look for the Project Name that
you just applied to the file containing the calendar. Place a tick in the box
next to its name and click OK. Now your file will know where to find the
NOTE: Sometimes the Visual Basic Editor is slow to recognise any
changes made to Project Names and the new name is either not shown in the
References dialog, or shown as "UNSAVED". You can prompt the dialog to
update by closing and restarting Excel (remember to save your work first!).
NOTE: Setting a reference to an external file only applies to
the workbook in which you set the reference. If you want another workbook
to use that same macro then it must have a reference to the file set too.