A Pop-up Calendar for Excel.
There are 2 methods to create Pop-up Calendar in xl.
Method 1.
Method 2. (The simplest way)
Method 1.
One of the biggest problems in maintaining "good" data is the entry of dates. People seem to get confused about entering dates. Should they enter dd/mm/yy or mm/dd/yy? Do they enter slashes or dashes or dots? And what was the date of the third Thursday in September last year anyway? What you really need is a calendar!
This tutorial shows you how to create a pop-up calendar using the Microsoft ActiveX Calendar Control that is installed with Excel. The user will be able to call up the calendar with a keyboard shortcut, from a toolbar button or menu, or from the right-click context menu. When the user selects a date it is automatically entered into the active cell on the worksheet...
Where Does the Code Go?
The code that creates and operates the calendar has to reside inside an Excel file. The question is which one? If you want the pop-up calendar to be available whenever you are working in Excel you should create it in Personal.xls, sometimes referred to as your Personal Macro Workbook, because this files exists to store code that you want to be available to all your Excel files. Personal.xls opens and is hidden each time Excel is started. Any macros and functions it contains are then available for use in any other workbook.
Alternatively, you could create your pop-up calendar in a specific workbook. It would be available whenever (and only) when that workbook was open, but also available to any other workbook that was open at the same time.
In this tutorial I will be using Personal.xls but if you choose to put your pop-up calendar somewhere else you can still follow the same instructions.
Prepare the Workbook (Personal.xls)
Build the Calendar and Write its Code
Step 1: Insert a New UserForm
The calendar is going to be displayed on a VBA UserForm. In the Project Explorer click on VBAProject (PERSONAL.XLS) then go to Insert > UserForm to open a new blank UserForm [click the thumbnail below to see a full-sized image of the Visual Basic Editor window]...
Make the following changes in the Properties Window...
Name: frmCalendar
Caption: Select a Date
Step 2: Locate the Calendar Control Tool
You need to draw a Calendar Control (active objects on forms are called "controls") on the UserForm, but the one you need is not normally included in the Toolbox. To install it, first make sure that the Toolbox is visible. If it isn't, switch it on with View > Toolbox. Then go to Tools > Additional Controls. In the dialog box find Calendar Control 8.0 (for Excel 97 and 2000, Calendar Control 10.0 for Excel 2002) and select it. Click the
NOTE: The Calendar Control is an ActiveX control (actually a file called mscal.ocx) supplied with Microsoft Office. It is normally installed with a standard installation of Microsoft Office Professional or Microsoft Access, but if you can't find it on the list you will need to get hold of a copy. If you are distributing your file, or planning to use it on more than one computer, you will also need to make sure that the host computer has the mscal.ocx file installed. You will find it on the CD that your copy of Microsoft Office Professional came on, or you can download a copy from here.
Step 3: Add a Calendar Control to the UserForm
Click the Calendar tool then click on the UserForm near its upper left corner to place a calendar on the UserForm.
Make sure the calendar is selected then take a look at the choices in the properties window. Click on the Custom category (near the top of the list) and click the [...] button to open the calendar control's custom properties dialog. You can format the calendar any way you like. I made the following changes...
General - Show Month/Year Title: No
Font - DayFont Size: Size 8, Not Bold
Font - GridFont: Size 8
Omitting the title and making the text smaller allowed me to resize the calendar to a smaller rectangle by dragging the resize handles (white rectangles)...
Now click on the UserForm itself so that you can see its resizing handles and resize it to fit the calendar...
To get a preview of how the calendar will look click the F5 key. When you do this the UserForm will open in Excel as it would in use. Click the UserForm's close button ([x]) to return to the Visual Basic Editor.
Step 4: Add a Command Button
If the user opens the calendar by mistake, they will be able to close it by clicking the [x] button without entering a date on the worksheet. Most users also expect a form or dialog box to close without making changes if they press the [ESCAPE] key on their keyboard. This doesn't happen by itself. You have to program the UserForm to respond to this event. To do this you will place a command button on the UserForm which closes it when clicked. By setting the button's Cancel property to Yes the effect of the user hitting the [ESCAPE] key will be to programmatically click the button. The user doesn't need to see the button, so you can hide it behind the calendar.
Click the Command Button tool on the toolbox then click on the calendar (just left of centre) to place a command button on the UserForm. Note that the command button places itself behind the calendar by default, although you can still see its outline when it is selected...
With the command button selected, make the following changes in the properties window... Name: cmdClose
Cancel: True
Step 5: Write Code for the Command Button
This step adds some code to the Click event of the command button so that when clicked (i.e. when the user hits the [ESCAPE] key) the UserForm closes without anything else happening.
Go to View > Code (Keys: F7) to open the UserForm's code window. If the command button was still selected there will already be an entry. If not, choose cmdClose from the left hand (General) drop-down list at the top of the code window. It should automatically select Click from the right-hand (Declarations) list. Place your cursor between the Sub... and End Sub lines and press the [TAB] key to indent your code. Type Unload Me. Your line of code will look like this:
Unload Me
End Sub
Use [CTRL]+[TAB] (or go to Window > frmCalendar(UserForm)) to switch back to the UserForm window and test the code. Press [F5] to open the form in Excel. Notice that the command button is invisible (it is behind the calendar). Press the [ESCAPE] key and see that the UserForm closes.
Step 6: Write Code for the Calendar
This step builds the code that will transfer the date that the user chooses to the active (selected) cell on the worksheet.
Return to the UserForm's code window. Open the left-hand (General) drop-down list at the top of the code window and choose Calendar1. If it is not already selected, choose Click in the right-hand list. This enters the start and end lines of the Calendar1_Click procedure.
Place the cursor between the two lines and press [TAB] to indent your code (indenting blocks of code is good practice - it makes the code easier to read) then type:
ActiveCell.Value = Calendar1.Value
...which transfers the date selected on the calendar to the active cell. On the next line type:
Unload Me
...which closes the form. Your code should look like this:
Private Sub Calendar1_Click()
ActiveCell.value = Value
Unload Me
End Sub
To make the calendar a bit more user-friendly you will now add some code that checks to see if the active cell already contains a date. If it does, then the calendar will show the same date when it opens. If there is no date in the cell (or if what is in the cell isn't a date) the calendar will show today's date. To do this we make use of the UserForm's Initialize event, which happens as the UserForm opens.
Open the left-hand drop-down list at the top of the code window and choose UserForm, then open the right-hand list and choose Initialize. (You will notice that when you chose "Initialize" the start and end lines for the UserForm_Click procedure were created. You don't need these so it is safe to delete them.)
Place your cursor between the start and end lines of the UserForm_Initialize procedure and type the following:
If IsDate(ActiveCell.Value) Then
Calendar1.Value = DateValue(ActiveCell.Value)
Else
Calendar1.Value = Date
End If
This IF Statement checks the active cell for a date. If it is a date then the calendar displays the same date. If not it uses the VBA Date function to display the current date. Your completed code should look like this:
Private Sub UserForm_Initialize()
If IsDate (ActiveCell.Value) Than
Calendar1.Value = DateValue (ActiveCell.Value)
Else
Calendar1.Value = Date
End If End Sub
Now you need to write some code to display the UserForm on demand. If you had to create Personal.xls you will already have a code module (Module1). Otherwise, or if you are using a different workbook, go to Insert > Module to create one. Double-click the name of the module in the Project Explorer window to open its code window (or right-click it and choose View Code).
Type the line Sub OpenCalendar and press enter. The Visual Basic Editor places a pair of brackets at the end of the line and adds the line End Sub, placing your cursor between them. Press [TAB] then type frmCalendar.Show. Your code should look like this:
Sub OpenCalendar()
frmCalendar.Show
End Sub
This simple procedure will display the UserForm when run from Excel.
Step 7: Test the Code
At this point it is a good idea to save your file in case something goes wrong! In the Visual Basic Editor window go to File > Save PERSONAL.XLS (or whatever your file is called).
When the calendar opens it will display today's date. Choose a date (NOTE: always choose month and year before choosing day because clicking on a day fires the Calendar1_Click event). When you click the day the calendar closes and enters your date into the cell.
Now select a cell that already contains a date and run the macro. When the calendar opens it displays the date that was already in the cell. If you wish you can choose another date, or you can press the [ESCAPE] key to close the calendar without making any changes.
Adding Extra Features
Adding a Toolbar Button or Menu Item
A useful addition is a toolbar button and/or menu item that lets the user call the macro that opens the UserForm. They don't need to know that they are calling a macro - they will just be using your new Insert Date tool!
To create a toolbar button:
Go to View > Toolbars > Customize and choose the Commands tab on the Customize dialog. In the left-hand window scroll down to Macros and select it. You will see two items in the right-hand window, one for a new button and the other for a new menu item.
Drag Custom Button from the dialog box to a position on one of your toolbars then release the mouse button to place the new button on the toolbar. You can rearrange and edit your toolbars and menus as much as you want when the Customize dialog is open...
>>>
You must link the button to the macro, and you will probably want to change its image too:
- Right-click the button and enter some text (such as Insert Date) in the text box next to Name, then press [ENTER]. This text will be the tool-tip that the user sees when they point at the button.
- Right-click the button and choose Assign Macro, choose the name of your macro (in this case PERSONAL.XLS!OpenCalendar) from the list, and click
.
Here's what my button looks like:
Make any other changes you want and click
To create a new menu item:
Open the Customize dialog as in the previous step and select the Macros category. This time drag Custom Menu Item from the dialog box to one of your menus, but don't release it yet. The menu will open and allow you to place your new menu item wherever you choose.
- Right-click the menu item and enter some text (such as &Date) in the text box next to Name, then press [ENTER]. This text will be the title of the menu item. The ampersand (&) does not appear itself but causes the following letter to be underlined (i.e. Date). This allows users to select the menu item by using their keyboard.
- You can have a button image too if you want. I used the same image as for my custom button. To do this right-click on the button and choose Copy Button Image then right-click on the menu item and choose Paste Button Image.
- Right-click the menu item and choose Assign Macro, choose the name of your macro (in this case PERSONAL.XLS!OpenCalendar) from the list, and click
.
Here's how it looks...
Close the Customize dialog box and test your menu item.
Assigning Keyboard Shortcut
You can also assign a keyboard shortcut. There are two ways to do this. You can do it manually or have the workbook assign a shortcut when it is opened. The advantage of the latter method is that, if the calendar is contained in its own workbook, the shortcut applies only when the workbook is open. It also means that the shortcut is applied automatically on which ever computer the file is being used - handy for files that are being distributed and for add-ins too.
To assign a shortcut manually:
In Excel go to Tools > Macro > Macros to open the Macro dialog. Select the name of your macro (in this case PERSONAL.XLS!OpenCalendar) then click the
To assign a shortcut with code:
In the Visual Basic Editor find the This Workbook entry for PERSONAL.XLS (or your chosen workbook) in the project explorer and double click it (or right-click and choose View Code) to open its code window. Open the left-hand (General) drop-down list at the top of the code window and choose Workbook. This creates the start and end lines for the Workbook_Open procedure. This is a special macro that runs automatically whenever the workbook is opened. Between the start and end lines type the line:
Application.OnKey "+^{C}", "Module1.OpenCalendar"
The + sign signifies Shift, the ^ signifies CTRL and {C} signifies the letter C. You can use any key combination you like. Your code should look something like this:
Private Sub WorkBook_Open()
Application.OnKey "+^{C}", "Module1.OpenCalendar"
End Sub
You will need to save and close the workbook and then reopen it before you can try it out. If you are using PERSONAL.XLS you will need to close Excel and reopen it. Save the file first!
Adding the Calendar to the Shortcut Menu
Using VBA you can add custom items to shortcut menus (the menu you see when you right-click on something, sometimes called context menus). Here's how to add an item to the "Cell" shortcut menu (the one which appears when you right-click on a worksheet cell). The menu item will call the macro that opens the calendar's UserForm.
Is is a good idea to place the code that creates the shortcut menu item in the Workbook_Open macro, so that the menu item is added whenever the workbook is opened. But to ensure that the item appears only once (the menu item is persistent - a duplicate would be created each time you opened the file!) you should also create a procedure to remove the menu item when the file closes. To do this you can create a Workbook_BeforeClose macro, which runs automatically when its host workbook closes.
Code to create the new shortcut menu item:
If you have created a keyboard shortcut with code (in the previous step) you will already have a Workbook_Open macro. If not, create one as described above (you can omit the line that assigns a keyboard shortcut if you want).
Add lines to the Workbook_Open procedure as follows:
If necessary, make an empty line above the existing code, and enter as the first line the variable declaration:
Dim NewControl as CommandBarControl
The next line of code, which can be entered below your shortcut key code, tells Excel that the variable is a new item (or control) to be added to the right-click menu of a cell:
Set NewControl = Application.CommandBars("Cell").Controls.Add
Now some code to describe the new item:
With NewControl
.Caption = "Insert Date"
.OnAction = "Module1.OpenCalendar"
.BeginGroup = True
End With
The Caption is the text which appears on the menu, and can be anything you want. Make sure that OnAction correctly specifies your macro's name and location. The optional BeginGroup puts a separator line above the new item on the menu. Your finished code should look like this:
Private Sub Workbook_Open()
Dim NewControl As CommandBarControl
Application.OnKey "+^{C}", "Module1.OpenCalendar"
Set NewControl = Application.CommandBars("Cell").Controls.Add
With NewControl
.Caption = "Insert Date"
.OnAction = "Module1.OpenCalendar"
.BeginGroup = True
End With
End Sub
Here's how the resulting shortcut menu looks:
Code to remove the new shortcut menu item:
This procedure reverses the action of the code in the previous step. This is necessary because items added to the shortcut menu will remain there even when the workbook that created them is not open. Choosing the shortcut would then result in an error (although not a serious one... the user would see a message to the effect that Excel couldn't find the macro). Also, because the workbook creates the menu item as it opens, you would end up with several copies of the item if you didn't remove it as the workbook closed.
In the same module as the Workbook_Open procedure use the drop-down lists to create a Workbook_BeforeClose procedure. Enter the line:
On Error Resume Next
...which tells Excel to ignore any error that might occur (i.e. if it tries to remove the menu item but can't find one). Then enter the line:
Application.CommandBars("Cell").Controls("Insert Date").Delete
...which removes your item from the shortcut menu of a cell. Your finished code should look like this:
Private Sub Workbook_BeforeClose (Cancle As Boolean)On Error Resume Next
Application.CommandBars("Cell").Controls("Insert Date").Delete
End Sub
TIP: If you should find yourself with unwanted copies of custom menu items on the shortcut menu (because, like me, you wanted to test the first procedure before you created the second one!). You can give the instruction from the Visual Basic Editor's Immediate Window (go to View > Immediate Window or Keys: CTRL+G). Click in the Immediate Window and type the line the starting Application... and ending ...Delete then press [ENTER].
Method 2. (The simplest way)
No comments:
Post a Comment