|   Home    |    Excel    |    Access   |   Word   |   Outlook   |   FrontPage   |   VBA   |   Downloads   |   Index   |
Notes

 

A Pop-up Calendar for Excel

Suitable for: Excel 2007, 2010
Click here for Excel 97, 2000, 2003

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!

NOTE: When I first wrote this tutorial I made use of the Microsoft Calendar Control, an ActiveX control that was installed along with Microsoft Office Professional (i.e. the version that included Microsoft Access). If you didn't have that version of Microsoft Office you could still download and install a copy of the ActiveX control. If you want to do that you can follow the original tutorial here. Since then Microsoft have created an alternative to the Calendar Control called the MonthView control. This new ActiveX control is similar in function to the Calendar Control and has the advantage that it is installed with all versions of Microsoft Office. In Microsoft Office 2007 you have the choice of which ActiveX control to use but in Microsoft Office 2010 you must use the MonthView control as described in this tutorial.

The tutorial shows you how to create a pop-up calendar using the Microsoft MonthView control that is installed with Excel. You will use the Visual Basic Editor to create a UserForm that displays a calendar. You will also write some VBA code to power the UserForm and to generate an additional item on the menu that appears when the user right-clicks a cell on an Excel worksheet. Clicking the menu item will display the calendar. When the user selects a date it is automatically entered into the active cell on the worksheet.

A pop-up calendar for entering dates into Excel cells.

What Will the Calendar Do?

The MonthView control has a number of useful features. In its standard format it displays a single month in calendar format (it can be set to display more than one if required). Either side of the month name is an arrow button which displays the previous or next month when clicked. Clicking on the month name opens a list of months so that you can quickly jump to a specific month. Clicking on the year number reveals a spinner which lets you change the year. The current date is always shown at the bottom of the calendar. Click it to jump to today's date on the calendar.

Click the arrows to go back or forward a month. Click the month name to change the month. Click the year number to change the year. Click today's date to jump to today.

Where Does the Code Go?

The pop-up calendar is created entirely with code and is generated when you need it. 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.xlsb, sometimes referred to as your Personal Macro Workbook, because this file exists to store code that you want to be available to all your Excel files. Personal.xlsb opens and is hidden each time Excel is started. Any macros and functions it contains are then available for use in any other workbook. To find out whether or not you already have a copy of Personal.xlsb read the instructions in New to VBA? further down the page.

However since the Personal Macro Workbook, as its name implies, is specific to your copy of Excel it will reside either on your computer's hard drive or within your personal profile on the network. It isn't the best place to put the code if you want other users to have access to the pop-up calendar. Instead, 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. A pop-up calendar created in an Excel template would be present in each workbook that was generated from the template. The most flexible option is to create an Excel Add-In. It's easy to do and will allow you to distribute your calendar to other users. You start by building the calendar exactly as described in this tutorial but in a new Excel workbook (not Personal.xlsb) which you then convert to an Excel Add-In. The last section of this tutorial takes you through the steps of creating an Excel Add-In.

In this tutorial I will be using Personal.xlsb but if you choose to put your pop-up calendar somewhere else you can still follow the same instructions.

New to VBA?

If you plan to create and edit macros or work with VBA code you will find it useful to enable the Developer tab. It offers quick access to many of the tools you will be using during your projects. If it isn't currently visible in your copy of Excel you can enable it by going to Excel Options.

  • In Excel 2007 click the Office Button and choose Excel Options. Click the Popular button, check the Show Developer tab in the Ribbon option then click OK to return to Excel.
  • In Excel 2010 go to the File tab and choose Options and select the Customize Ribbon section. On the right side of the window under Customize the Ribbon make sure that Main Tabs is selected from the dropdown then check the Developer option and click OK to return to Excel.
Enable the Developer tab in Excel 2007. Enable the Developer tab in Excel 2010.

If you have never recorded a macro on your current copy of Excel then you probably won't have a copy of Personal.xlsb yet. If a copy exists it will be visible in the Project Explorer window of the Visual Basic Editor. If you don't have one it takes just a moment to create: On the Developer tab click the Record Macro button. When the Record Macro dialog appears choose to store the macro in the Personal Macro Workbook then click OK. Now click the Stop Recording button on the Developer tab. You have just recorded an empty macro but that action was sufficient to prompt Excel to create a copy of Personal.xlsb to store it in. You can delete the macro later but now you have somewhere to create your pop-up calendar.

Build the Calendar

The first step is to build the calendar. When you have done that you will go on to write the VBA code that will make it work. If you plan to create an Excel Add-In you should open a new empty workbook. If you want your calendar to reside in a specific workbook then make sure that workbook is open. In Excel open the Visual Basic Editor using the keyboard shortcut [ALT]+[F11] or click the Visual Basic button on the Developer tab of the Ribbon.

IMPORTANT: Remember to save your work regularly. Since a UserForm and its associated code resides within an Excel workbook the they are saved when you save the workbook and vice-versa. You can save from within Excel or from within the Visual Basic Editor window in the usual way by clicking the Save button. Excel will warn you if there are any unsaved changes when you close the workbook. If you are working in a regular Excel workbook (and not in Personal.xlsb) you should save your workbook as an Excel Macro Enabled Workbook (*.xlsm). Failure to do this will result in Excel discarding your code.

Step 1. Create a new UserForm

You are going to place the pop-up calendar on a UserForm which is a kind of dialog box that you can build and program with VBA. Go to the Project Explorer window of the Visual Basic Editor. The Project Explorer is usually located in the upper left corner of the Visual Basic Editor window. If you can't see it, switch it on from the View menu. Right-click on the name of the workbook in which you want to create the pop-up calendar then choose Insert and UserForm.

Insert a new UserForm.

A new empty UserForm will appear in the main window of the Visual Basic Editor together with the Toolbox containing buttons for the most commonly used objects (called controls) that can be placed on a form. You can switch the Toolbox on and off from a button on the toolbar. You will also notice that the Toolbox disappears when the UserForm is not selected. If this happens just click on the UserForm and the Toolbox will reappear.

Step 2. Rename and caption the UserForm

When the UserForm is selected the Properties Window of the Visual Basic Editor displays a list of all the UserForm's properties. The Properties Window is normally located in the lower left corner of the Visual Basic Editor window. If you can't see it switch it on from the View menu. Excel automatically names and captions (where appropriate) new objects. You will see that the UserForm has been given the name and caption UserForm1. It is good practice to give objects more meaningful names so go to the Properties Window and change the Name property to frmCalendar and the Caption property to Pick a Date...

IMPORTANT: If you choose to use different names from those suggested here remember to modify any code that refers to these objects by name.

You can test the Userform from the Visual Basic Editor now (and at any stage) by pressing [F5] on your keyboard or clicking the Run button on the toolbar. Doing this opens the UserForm in Excel so that you can check the progress of your design. Closing the UserForm (click the [X] in its upper-right corner) returns you to the Visual Basic Editor.

Step 3: Place a Close button on the UserForm

You might think the calendar doesn't need a Close button since there is already one in the upper-right corner of the UserForm, and our code will close the form automatically after a date has been chosen. But doing so will add a useful feature that most users take for granted, that is to close the calendar if they press the [Esc] key on their keyboard.

Click the CommandButton button on the Toolbox then click on the center of the UserForm. The dots on the UserForm represent a grid to help you align objects neatly. Point at the dotted border of the new command button and drag it so that it is located two grid points down from the top and two grid points in from the left of the UserForm.

Now use the Properties Window to change the command button's Name property to cmdClose, change its Caption property to Close and set its Cancel property to True. When the Cancel property of a command button is set to True the button gets clicked automatically when the user presses the [Esc] key on their keyboard.

The UserForm has a new caption and a Close button.

Step 4: Code and test the Close button

In this step you will attach some code to the Cancel button so that when it is clicked the UserForm will close, thus closing the pop-up calendar. In the Visual Basic Editor double-click the Close command button. This opens the UserForm's code window with an empty Event Procedure for the Click event of the command button (an Event Procedure is a collection of commands that are executed when a particular event happens - in this case when the cmdClose button is clicked).

Between the Sub... and End Sub lines enter the statement:

      Unload Me

This instructs Excel to close the UserForm. The completed code should look like this:

Private Sub cmdClose_Click()
   Unload Me
End Sub

Return to the UserForm design window by double-clicking its name in the Project Explorer or by using the keyboard shortcut [CTRL]+[TAB] to switch windows, then test the UserForm as before by pressing [F5] or clicking the Run button on the toolbar. You should be able to close the UserForm from Excel by either clicking the Close button or by pressing the [ESC] key.

HOW THE CODE WORKS
The command Unload causes a UserForm to close and drop out of memory (the command Hide can be used to be remove it temporarily from view yet have it remain open in memory so that it can be reopened without losing its data). The word Me is a quick way of referring to the current UserForm without having to use its full name. I could have written Unload frmCalendar. Since the form is usually referred to frequently in the code using Me simplifies code writing and saves a lot of editing if the UserForm's name is ever changed.

Step 5: Locate the MonthView control

The MonthView control will provide the calendar that you will place on the UserForm. It is not normally present on the Toolbox so you will have to ask for it. Right-click on the Toolbox and choose Additional Controls. Scroll down the list in the Additional Controls dialog until you find Microsoft MonthView Control 6.0 (the version number might be different depending on your version of Microsoft Office) and check the box next to its name, then click OK to close the dialog. You will see that a button for the new control has been added to the toolbox.

Add the MonthView control to the Toolbox.

Step 6: Place a calendar on the UserForm and set its properties

Click the MonthView button on the Toolbox then click on the UserForm to create a calendar on the form. Drag the calendar into the upper left corner to the UserForm. The calendar will cover the Close button you created earlier. This is intentional because the Close button will be operated by the user pressing their [ESC] key and it does not need to be visible.

When the MonthView control is selected the Properties Window displays its properties. By default it has a sunken border. I don't like this effect so I have changed the Appearance property to 0 - cc2Flat. There are many other properties you can modify. The MonthColumns and MonthRows properties allow you to display more than one month at a time. The ShowToday property lets you choose whether or not to highlight and display the current date.

Position the MonthView control on the UserForm.

Step 7: Resize the UserForm

The final step in designing the calendar is to change the size of the UserForm to match the dimensions of the calendar. Click on the background of the UserForm to select the form then use the resizing handles (white rectangles located around the dotted border) to drag the edges of the UserForm to the required size. Finally, run the form to check that it looks OK.

Resize the UserForm to match the calendar.

Step 8: Write the code to enter a date

In this step you will create the code that writes a date on to the worksheet when the user clicks one of the day buttons on the calendar. Double-click the MonthView control to open the code window. This opens the UserForm's code window with an empty Event Procedure for the DateClick event of the MonthView control.

You have a couple of choices now depending upon what you want to happen when the user chooses a date. If you want the calendar to enter the chosen date only in the active cell, which is the selected cell when only a single cell is selected or only the active cell if a block of cells or multiple cells are selected, then use this method. Between the Sub... and End Sub lines enter the statements:

      On Error Resume Next
      ActiveCell.Value = DateClicked
      Unload Me

This instructs Excel to enter the chosen date into the currently active cell and the close the UserForm. The completed code should look like this:

Private Sub MonthView1_DateClick(ByVal DateClicked As Date)
   On Error Resume Next
   ActiveCell.Value = DateClicked
   Unload Me
End Sub

If you want the calendar to enter the chosen date in all the currently selected cells, whether this is a block of cells, a multiple selection, or just a single cell if only one is selected, then use this method. This code is more versatile so I would probably do it this way unless I had a particular reason not to do so. Between the Sub... and End Sub lines enter the statements:

      On Error Resume Next
      Dim cell As Object
      For Each cell In Selection.Cells
           cell.Value = DateClicked
      Next cell
      Unload Me

This instructs Excel to write a date into each of the selected cells. Your code should look like this:

Private Sub MonthView1_DateClick(ByVal DateClicked As Date)
   On Error Resume Next
   Dim cell As Object
   For Each cell In Selection.Cells
      cell.Value = DateClicked
   Next cell
   Unload Me
End Sub

Test the code by running the calendar as before, with one or several cells selected. If it fails to work properly then check your typing!

HOW THE CODE WORKS
Although this is a very simple operation, in both cases I have added the statement On Error Resume Next which tells Excel to ignore any error that might occur. This will prevent the code from crashing if, for example, the selected cell or one of the cells in a selection is locked. The code makes use of the DateClicked parameter which is given the date chosen when the user clicks one of the day buttons. In the first example that value is simply written into the active cell before the UserForm is closed. In the second example the code first declares a variable that represents a single cell. It then employs a For...Next code loop to visit each cell in the current selection, entering the value into each as it goes, before finally closing the UserForm.

Step 9: Synchronize the calendar with the worksheet

I nearly didn't include this step, which is optional anyway, because whilst it works perfectly on the old version of my pop-up calendar it doesn't work completely as it should in this version. The idea is that, if the active cell already contains a date, the calendar opens to display that same date. I have been able to make the calendar display the correct month and year but despite my best efforts can't get it to highlight the specific day. The MonthView control has a DayBold property which is "supposed" to be controllable with code - you tell the calendar which day number to display in bold and it does so - but in the current context I just can't make it work. I've searched for a solution without success so if you find one please let me know! Here's how to have the calendar open at the correct year and month...

Open the UserForm's code window by right-clicking on its name in the Project Explorer and choosing View Code or pressing the [F7] key. At the top of the code window are two drop-down lists. From the left-hand list choose UserForm the from the right-hand list choose Initialize. This creates and empty event procedure for the UserForm_Initialize event (if a UserForm_Click event is also created you can delete it). Between the Sub... and End Sub statements type:

      If IsDate(ActiveCell.Value) Then
            Me.MonthView1.Value = ActiveCell.Value
      End If

Your completed code should look like this:

Private Sub UserForm_Initialize()
   If IsDate(ActiveCell.Value) Then
      Me.MonthView1.Value = ActiveCell.Value
   End If
End Sub

Test the code in different scenarios: by opening the calendar when the active cell in Excel is empty; when it contains something that is not a date (a number or some text); and when it contains a date different from the current date. If the active cell is empty, or contains text or a number, the calendar should open to show the current date. If the active cell already contains a date the calendar should open to show the same year and month.

The calendar displays today's date.The calendar matches its date with the active cell.

HOW THE CODE WORKS
The Initialize event happens as the UserForm opens. It is used to prepare the form for use with such tasks as filling lists and setting starting values for textboxes. Here it is being used set the initial value of the MonthView control. The code uses the IsDate function to check whether or not the active cell contains a date. This is used as the condition for an If Statement so that, if the function returns True, the value of the calendar is set to the same date. If the function returns False then no date was found so nothing changes and the calendar opens with its default date.

Step 10: Write the code to open the calendar

Until now you have been opening the pop-up calendar from from the Visual Basic Editor but we need a way to open it from Excel. This will take the form of a simple macro. In the Project Explorer right-click on the name of the workbook in which you created the UserForm then choose Insert and Module. This creates a new code module which then opens in the main window of the Visual Basic Editor. Type:

      Sub OpenCalendar

and press [Enter]. Excel places a pair of brackets after your typing and after an empty line adds the line End Sub. Place your cursor in the empty line between Sub... and End Sub and enter the following statement:

      frmCalendar.Show

If you gave your UserForm a different name make sure you use it here. The completed code should look like this:

Sub OpenCalendar()
   frmCalendar.Show
End Sub

You can test your macro by going to Excel and clicking the Macros button on the View tab or the Developer tab. Your macro will be listed in the Macro dialog box. Select it then click Run to open the calendar.

Test the macro that opens the calendar

HOW THE CODE WORKS
The Show command is the reverse of the Hide command mentioned earlier. But this time you can't use Me to refer to the UserForm because it is not the object in which this bit of code resides. The UserForm has to be referred to by name. There is also a Load command which opens a Userform into memory but does not make it visible. It is unnecessary to use it here because, if a UserForm is not already open in memory when the Show command is given, Excel assumes that it must Load it first and does so automatically.

Step 11: Write the code for the shortcut menu

Now that there is a macro to open the calendar we can add some features to make it more convenient for the user to run it. One way is to add a new command to the Cell context menu, the one that the user sees after right-clicking on a cell. This means writing some code to add a new item to the menu when the file containing the calendar opens, and some more code to remove the menu item when the file closes. This code has to run automatically when the file opens or closes so we make use of special event procedures that are located in the ThisWorkbook code module.

In the Visual Basic Editor go to the Project Explorer and click the plus sign [+] next to the folder marked Microsoft Excel Objects under the name of the file containing your calendar. When the folder opens double-click the item marked ThisWorkbook to open the code module in the main window. Choose Workbook from the left-hand drop-down list at the top of the window. This automatically creates an empty event procedure for the Workbook_Open event. Between the Sub... and End Sub statements type:

      On Error Resume Next
      Dim NewControl As CommandBarControl
      Application.CommandBars("Cell").Controls("Insert Date").Delete
      Set NewControl = Application.CommandBars("Cell").Controls.Add
      With NewControl
            .Caption = "Insert Date"
            .OnAction = "Module1.OpenCalendar"
            .BeginGroup = True
      End With

Your completed code should look like this:

Private Sub Workbook_Open()
   On Error Resume Next
   Dim NewControl As CommandBarControl
   Application.CommandBars("Cell").Controls("Insert Date").Delete
   Set NewControl = Application.CommandBars("Cell").Controls.Add
   With NewControl
      .Caption = "Insert Date"
      .OnAction = "Module1.OpenCalendar"
      .BeginGroup = True
   End With
End Sub

IMPORTANT: If you changed any of the names within your project you must make sure you also edit the code so that the matching names are used. So, if you called your macro anything other than "OpenCalendar" or the module in which it is located is called anything other than "Module1", you should amend the code accordingly. Make sure, for example, that the text "Insert Date" is spelled the same way each time it is used. Simple errors like this are the most common reason for code not working

HOW THE CODE WORKS
The Workbook_Open event happens automatically when a workbook opens in Excel. As before, the code starts with an instruction to ignore any errors that might occur (you should always use this command with caution and only when you are sure that nothing untoward can happen if an error is ignored). Then a Dim statement is used to declare a variable which I have called "NewControl" and which represents a menu item. Before proceeding to create the new menu item the next statement deletes the item from the menu. This seems illogical but it is a safety measure to make sure that, if Excel did not close properly last time, we are not left with multiple copies of the menu item. If, as expected, Excel closed properly and the menu item was deleted then this statement would cause an error to occur, hence the error handler at the start. The next statement adds the item to the menu. A With Statement (a way of grouping together a number of commands relating to the same thing) is used to set the properties of the new menu item.

Now go to the right-hand drop-down list at the top of the code window and choose BeforeClose to create an event procedure for the Workbook_BeforeClose event. Between the Sub... and End Sub statements type:

      On Error Resume Next
      Application.CommandBars("Cell").Controls("Insert Date").Delete

Your completed code should look like this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   On Error Resume Next
   Application.CommandBars("Cell").Controls("Insert Date").Delete
End Sub

HOW THE CODE WORKS
The Workbook_BeforeClose event happens automatically as a workbook closes. This procedure simply deletes the new item from the menu and is protected with a simple error handler.

Step 12: Add a keyboard shortcut to activate the calendar

If you like using keyboard shortcuts then you might like to have one automatically assigned to your pop-up calendar. This requires an additional command statement to be added to the Workbook_Open event procedure. Add the following line to the Workbook_Open event procedure. Either below the Dim... statement or immediately above the End Sub statement:

      Application.OnKey "+^{C}", "Module1.OpenCalendar"

You also need to cancel the shortcut assignment when the file containing the calendar closes so add the following line to the Workbook_BeforeClose event procedure:

      Application.OnKey "+^{C}"

The completed code should look like this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   On Error Resume Next
   Application.OnKey "+^{C}"
   Application.CommandBars("Cell").Controls("Insert Date").Delete
End Sub

Private Sub Workbook_Open()
   On Error Resume Next
   Dim NewControl As CommandBarControl
   Application.OnKey "+^{C}", "Module1.OpenCalendar"
   Application.CommandBars("Cell").Controls("Insert Date").Delete
   Set NewControl = Application.CommandBars("Cell").Controls.Add
   With NewControl
      .Caption = "Insert Date"
      .OnAction = "Module1.OpenCalendar"
      .BeginGroup = True
   End With
End Sub

HOW THE CODE WORKS
The OnKey command is used to instruct Excel to run a specific macro when a particular combination of keys is pressed. The key combination is defined by a code, in this case +^{C} which represents the shortcut [SHIFT]+[CTRL]+[C]. The plus sign represents the [SHIFT] key, the caret (^) represents the [CONTROL] or [CTRL] key, and the {C} represents the letter C. If you prefer a different shortcut then you can specify something else. If you would like to see a complete list of the key codes that can be used go to your code module and place you cursor within the word OnKey in the code, then press [F1]. This opens Help at the appropriate page. The OnKey command is used in the Workbook_Open event procedure to assign the keyboard shortcut to the macro that opens the calendar, and again in the Workbook_BeforeClose event procedure, but this time without the macro assignment, to cancel the assignment when it is no longer needed.

Step 13: Test the code

Before testing your code you should CHECK YOUR TYPING, especially anything you have typed in quotes, such as the caption you used for the menu item! The Visual Basic Editor is good at spotting coding errors but can't check your text entries. It is far better to find and correct any errors now than after your code has crashed and maybe caused problems in your worksheet. You can additionally check any new code by compiling it. Open the Debug menu and choose Compile VBA Project. If you get no messages then the editor has not found any problems. If the Compile... entry is disabled it means that there is nothing new to compile.

Compile and Save your code then close Excel. This ensures that Personal.xlsb is closed. If you are working in a file other than your Personal.xlsb then you need only close and save that file. Re-open Excel (or re-open the file in which you created the calendar if it was not Personal.xlsb) and right-click on any cell. You should see the command Insert Date at the bottom of the context menu. Choose it to make your calendar appear. When you pick a date from the calendar the date will be written into the cell and the calendar will close. If you added the keyboard shortcut to open the calendar then test that too by holding down the [CONTROL] and [SHIFT] keys and pressing [C].

If any of this does not work then the most likely problem is a typing error in your code. Go back and check it thoroughly!

The new command appears on the cell's right-click menu.

Step 14: Password protect the code

If it is likely that other users will have access to your code projects and particularly if you intend to distribute your project either as a shared file, template or Add-In, it is advisable to protect your code with a password. This has a number of advantages. It prevents a third party from viewing and possibly interfering with the code. It also guarantees that, in the unfortunate event that an unforeseen error causes the code to crash, the user does not find themselves in the Visual Basic Editor looking at a stalled code module and not knowing what to do next.

In the Visual Basic Editor go to the Project Explorer then right-click on the name of the file containing your pop-up calendar project and choose VBAProject Properties. Select the Protection tab of the Properties dialog and enter then confirm your password. Place a tick in the Lock project for viewing check box then click OK and Save the file.

Password protect the code.

The password protection comes into operation after the protected file has been closed and re-opened. If you try to view the code of a protected code project the Visual Basic Editor requires you first to supply the password.

A password is required to view the code.

IMPORTANT: Don't forget the password! If you lose the password to a VBA code project you will permanently lock yourself out of the code. There is no easy way to recover the password of a protected file which, after all, is the point of protecting it.

That's it! You've finished and should have a fully-functioning pop-up calendar. If you want to continue and turn it into an Add-In then read on...

Building an Excel Add-In

The following steps explain how to convert your project into an Add-In which you can use yourself or distribute to other users. If you have been working so far in Personal.xlsb you don't need to build the calendar again. Just copy or move your UserForm and associated code into a regular Excel workbook. It isn't difficult to do. Open a new, empty workbook in Excel then in the Visual Basic Editor go to the Properties Window and drag the UserForm from Personal.xlsb to your new workbook where Excel will create a copy of it. All the UserForm's code will automatically go with it. Do the same for the module in which you created the macro that opens the calendar (Module1 in this example). Then copy the Workbook_Open and Workbook_BeforeClose code from the ThisWorkbook module in Personal.xlsb to the ThisWorkbook module of your new workbook.

IMPORTANT: If you plan to use the Add-In yourself you should delete the UserForm and all the associated code from Personal.xlsb to eliminate any risk of conflicts between them.

If you have been working in a regular Excel workbook then you don't need to do anything else, just follow on from here.

Step 15: Save the file and define its properties

If you have been working in a regular Excel workbook you will already have saved the file. If not, or if you have just copied everything into a new workbook from Personal.xlsb then you need to save it now. Save the file as an Excel Macro Enabled Workbook (*.xlsm) and call it something like Calendar.xlsm. It is possible to miss out this step but I recommend it because it gives you a back-up copy of your work and also allows you to add a meaningful name and a description to your Add-In. Then Close the workbook.

Use Windows Explorer to find the workbook you just saved then right-click on it and choose Properties. In the File Properties dialog click the Summary tab and enter some suitable information about your Add-In. Alternatively you can you can access the file properties from within Excel when the file is open:

  • In Excel 2007 click the Office Button then choose Prepare and Properties. In the Properties pane click Document Properties then Advanced Properties to open the Properties dialog.
  • In Excel 2010 go to the File tab, click Info then choose Properties and Advanced Properties to open the Properties dialog.

The Title property will become the name of the Add-In (as distinct from its filename) and the Comments property will be used to provide the user with a brief description of its features. After defining the properties remember to save the file!

Add a name and description to the file.

Step 16: Create the Add-In file

To create the Add-In open your Calendar.xlsm file in Excel then open the Save As dialog box:

  • In Excel 2007 click the Office Button then choose Save As and Other Formats.
  • In Excel 2010 go to the File tab and choose Save As.

If you want to give your Add-In a different filename you can do it now. Open the Save as type dropdown and choose Excel Add-In (*.xlam) from the list then click Save. Excel creates a copy of the file (the original remains unchanged) as an Add-In and automatically stores it in the Add-Ins folder. The location of the Add-Ins folder might be different depending on your particular set-up but it is usually found at: C:\Users\<Username>\AppData\Roaming\Microsoft\Add-Ins.

Save the file as an Add-In.

The location of an Add-In is important but not critical since, as you will see shortly, you can tell Excel where to find an Add-In if it is not in the usual place. This is useful if, for example, you want to store your Add-Ins in a network location.

NOTE: You will see that there is also the option to save the file as an Excel 97-2003 Add-In (*.xla). Don't do this unless you know that it is going to be used in an older version of Excel (Excel 2003 or earlier). The Add-In needs to be in the right format for the version of Excel in which it will be used.

Step 17: Activate the Add-In

Now that you have built the Add-In it must be activated in Excel before you can use it. If you are going to distribute your Add-In the users will need to do this on each computer in which the Add-In is installed.

  • In Excel 2007 click the Office Button and choose Excel Options.
  • In Excel 2010 go to the File tab and choose Options.

Go to the Add-Ins section of the Excel Options dialog. If you have installed the Add-In file in the default Add-Ins folder you will see it listed here under the Inactive Application Add-Ins heading (don't worry if you put it somewhere else - you will be able to browse for it later). You can click on its name here to view details of its location and the properties you assigned earlier.

The Add-In appears in the Inactive Application Add-Ins list.

Make sure that Excel Add-Ins is selected in the Manage list then click Go to open the Add-Ins dialog in Excel. If you installed the Add-In in the default Add-Ins folder it will be listed in the dialog box. Otherwise, click the Browse button to locate and select your Add-In file which will appear in the dialog when you have done so. You will also see any other Add-Ins that have already been installed including those that come pre-installed with Excel. Selecting one displays its features at the bottom of the dialog box. Now that Excel knows about your Add-In you can switch it on and off from here. Doing so effectively opens and closes the Add-In file in Excel. To switch your Add-In on place a tick in the box next to its name and click OK.

The Add-Ins dialog in Excel.

NOTE: In Excel 2010 the Developer tab of the Ribbon has a button (marked Add-Ins) that takes you direct to the Add-Ins dialog.

The pop-up calendar is now ready for use and will remain so each time you use Excel until you deactivate it by un-checking the box next to its name in the Add-Ins dialog box. If you return to the Add-Ins section of the Excel Options dialog you will see that your Add-In now appears under the heading Active Application Add-Ins.

The Add-In appears in the Active Application Add-Ins list.

Download a Sample File

Before downloading any files from this site please read the Disclaimer and Copyright notices on my Downloads page. If you download a file from this site it is assumed that you have read, understood and agreed to the terms set out in the notices.

So, maybe you are too busy or just can't be bothered to build one for yourself. Don't worry, you can download a ready-made copy of the Pop-up Calendar Add-In or an Excel workbook containing a functioning Pop-up Calendar right here. The files are not password protected so you can view and modify the code if you wish. You are strongly advised to password protect the code as described earlier if you plan to distribute these files to other users.

The files are supplied in .zip format. To download a file right-click on the link and choose Save target as... (in Internet Explorer) or Save Link As... (in Firefox) and save the .zip file to your hard disk. You should extract the Add-In or workbook from the .zip file before using it. Right-click on the .zip file and choose Extract All... then follow the instructions.

 

^ top