| 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 EventsThe 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" EventAs 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 cells:
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" EventThis 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" EventWhen 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:
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 FileWhere 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 ReferenceSetting 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:
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.
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 | ||||
|
©2004 Martin Green. All rights reserved. martin@fontstuff.com
|
