|   Home    |    Excel    |    Access   |   Word   |   Outlook   |   FrontPage   |   VBA   |   Downloads   |   Index   |
Mailbag...

 

How Can I Make my Excel Calendar Appear Automatically When the User Enters a Cell?

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:

Code for displaying the calendar when the user selects a specific cell

If you want the procedure to apply to more than one cell you can add a condition to the If Statement, for example:

Code for displaying the calendar when the user selects one of the cells specified

A Case Statement is less clumsy when applying the procedure to a long list of cells:

Code for displaying the calendar when the user selects one of the cells specified

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 UserForm).

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:

Code for displaying the calendar when the user double-clicks on a specified cell

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 instead:

Code for displaying the calendar when the user right-clicks on the specified cell

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...

Selecting an event procedure in the worksheet code module window

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.

All new projects are given the name "VBAProject"

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:

  1. In the Project Explorer window of the Visual Basic Editor right-click on the name of the file and choose VBAProject Properties...
  2. 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:
Enter a new Project Name in the properties window > > > The new Project Name is shown in the Project Explorer
  1. 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 OpenCalendar macro.

Locate and tick the project that contains the calendar

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.

 

^ top
 

 

 

 

 

Hit Counter