The Budget worksheet in the Chapter11 workbook shows detailed budget information for the year 2007. It includes both detail and summary rows.
Suppose that you need to print different versions of the budget. The managers want a version that shows only the summary rows. The data entry person wants a version that shows only the detail rows, without the totals. The budget analyst wants both the detail and the summary rows, but doesn’t want to see months that are completed.
To make it easy to print the various versions of the report, you can create a user form. Here’s the overall strategy for creating the form:
Design the form. The layout of the form is the first thing that the user sees, and it should suggest how to use the form. How the form looks and acts is called the user interface. The easiest way to design a form in Microsoft Visual Basic for Applications (VBA) is to just jump in and create it.
Create the macros that make the form work. These are the procedures that interact with Microsoft Office Excel objects. The tasks that the form executes are called its functionality. Adding functionality might involve making changes to the worksheet to enable the macros to work.
Make the form run the macros, and provide a way to show the form. Integrating the user interface with the functionality of the form is the final step, which is called implementation.
If you want to create a more sophisticated user interface for an Excel application, you can use Microsoft Visual Studio 2005, together with the Visual Studio Tools for Office (VSTO), to create a managed code (Microsoft .NET) application that can control the Excel object model.
The process of designing the form’s user interface can help you figure out what functionality you need to develop.
With the Chapter11 workbook open, right-click the Budget worksheet tab, and click View Code to open the Visual Basic editor.
On the Insert menu, click UserForm to create a new, empty user form.
The form is stored in your project just as a module is. You can run the form from VBA in the same way that you run a macro.
Click the Run Sub/UserForm button to display the form, and then click the Close button to close it.
By default, the caption of the form is UserForm1. If you create a second form, itwill have the caption UserForm2, and so on-similar to the way Excel creates new default worksheet names. The caption is a property; you can change the caption in the Properties window.
Click the Properties Window button, and change the value of the Caption property to Print Options.
The caption changes in the form as you change the value in the Properties window.
Change the value of the (Name) property to frmPrint.
The Name property has parentheses around it to make it appear at the top of the list. The prefix frm is short for form. The Print part of the name tells you the intended purpose of the form. If you ever need to refer to the form inside a procedure, you can use this meaningful name.
That’s all there is to creating a user form! Of course, it’s not much good until you put something in it.
Your goal in creating this form is to let a user choose one of three layouts when printing the report: all the rows, only the summary rows, or only the detail rows. Option buttons provide a way to select a single item from a short, predefined list. Generally, option buttons go inside a frame.
When VBA displayed the user form, it automatically displayed the Toolbox for forms. This Toolbox contains the same controls that are in the ActiveX Controls collection on the Developer tab of the Ribbon in Excel. You can use the Toolbox to add ActiveX controls to a form.
|See Also|| |
For information about putting ActiveX controls on a worksheet, see the section titled “Creating an Error-Resistant Loan Payment Calculator” in Chapter 10, “Use Dialog Box Controls on a Worksheet.”
Activate the Form window. If you don’t see the Toolbox, click the Toolbox button on the Standard toolbar to display it.
In the Toolbox, click the Frame button, and then click near the upper-left corner of the form.
A large frame control appears on the form. You can move or resize the frame later.
Your next task is to add the option buttons. You can avoid having to click the control button on the Toolbox each time you add a button by double-clicking the control button. Double-clicking activates the button until you click it again.
Double-click the OptionButton button, click in three places on the form to create three buttons, and then click the OptionButton button again to turn it off.
You don’t need to position the controls exactly.
Activate the Properties window, and select Frame1 from the list at the top.
Type Rows as the value of the Caption property and grpRows as the value of the Name property.
The prefix grp is short for group, which is an old name for a frame.
In the list at the top of the window, select OptionButton1.
Type All as the value of the Caption property and optAll as the value of the Name property.
With the optAll control still selected, type True as the value of the Value property.
Setting the Value property to True makes this the default option.
Give the second option button the caption Summary and the name optSummary.
Give the third option button the caption Detail and the name optDetail.
Select all three option buttons by clicking between the bottom option button and the bottom of the frame and dragging a rectangle that touches each of the option button captions.
The Format menu provides powerful tools for getting the controls on a form to line up properly.
On the Format menu in the Visual Basic editor, point to Vertical Spacing, and then click Remove.
On the Format menu, point to Align, and then click Lefts.
On the Format menu, click Size To Fit.
Finally drag the group of controls to the upper-left corner of the frame, and resize the frame so that it fits around the option buttons.
Save the workbook, press F5 to see how the option buttons will look (by clicking the option buttons), and then close the Print Options window.
A frame with a set of option buttons is a good user interface for selecting a single option from a predefined list.
Your form needs some way for you to specify whether to print all the months or only the remaining months. This is basically a “yes or no” choice. The best control for a “yes or no” choice is a check box. When the check box is selected, the macro will print starting with the current month.
Even though the budget analyst says that the report should start with the current month, you know that exceptions inevitably will arise. Therefore, in addition to the check box, you should add a text box that lets you specify a different start month, just to be prepared.
With the form window visible, click the CheckBox button in the Toolbox, and then click below the frame on the form where you want the check box to appear.
In the Properties window, change the caption for the check box to Start with month and change the name to chkMonth.
Double-click the size handle on the right of the check box selection rectangle to shrink the rectangle to fit the caption.
You’ll now add the text box for the month immediately after the caption for the check box so that the contents appear to complete the Start with month caption.
Click the TextBox button in the Toolbox, and then click to the right of the check box caption.
Change the text box name to txtMonth, set the Value property to 7/1/2007, and then change the Enabled property to False.
You won’t need to change the value of the month if the check box is cleared. Setting the Enabled property to False makes the contents of the box appear gray. You want the text box to become enabled whenever the user selects the check box. This is a job for an event.
Double-click the chkMonth check box control.
A new window captioned as frmPrint (Code) appears. It contains a new event handler procedure, chkMonth_Click. The Click event is the default event for a check box.
Insert the following statement as the body of the new chkMonth_Click procedure:
txtMonth.Enabled = chkMonth.Value
This statement enables the text box whenever the check box is selected and disables the text box whenever the check box is cleared.
Save the workbook, press F5 to run the form, and click the check box a couple of times to confirm that the enabled state of the text box always matches the value of the check box.
When the check box is cleared, you can’t change the date. When it’s selected, you can.
Close the form.
If the text box does not become enabled, make sure that you added the event handler to the check box, not to the text box.
Adding an event to the check box control makes the user interface work better, but it doesn’t change anything in Excel. Even though the event is VBA code, it is still part of the user interface of the form, not part of its functionality.
When you created the month text box, you assigned 7/1/2007 as a default date. Since most of the time you’ll want the current month in that box, you can make the form easier to use by initializing the text box with the current month. To do so, you must calculate the appropriate date for the text box at the time you display the form.
Double-click the background of the form.
A new procedure named UserForm_Click appears.
The name of the object for a form is always UserForm. No matter what name you give the form, the event handler procedures always use the name UserForm. The default event for a form is Click, but because you don’t want to wait until the user clicks the form to initialize the month, you need a different event.
From the Procedures list, select the Initialize event. After the UserForm_Initialize procedure appears, delete the UserForm_Click procedure.
Enter the following statement as the body of the procedure:
txtMonth.Value = Date
Date is a built-in Visual Basic function that returns the current date, based on your computer’s internal clock.
Press F5 to run the form.
The purpose of the date is to identify the month you want the report to start with. You’ll create a macro that searches the top row of the worksheet to find a date that matches the one in the text box. The dates in the top row of the worksheet are all for the first day of the month. To find a match, therefore, the date in the text box must be for the first day of the month as well. The date that the macro puts in the text box, however, is the current date. Because it’s highly unlikely that the current date is the first day of the month, you need a way to convert the current date to the first day of the current month.
Close the form, and then double-click the background to get back to the UserForm_Initialize procedure.
You’re now going to create a custom function that will convert any date into the first day of the month.
Below the UserForm_Initialize procedure, add this custom function:
Function StartOfMonth(InputDate) If IsDate(InputDate) Then StartOfMonth = DateSerial _ (Year(InputDate), Month(InputDate), 1) Else StartOfMonth = Empty End If End Function
This function accepts an input date as an argument. It first checks to see whether the input date is a date or can be turned into one. If it can, the function extracts the year and the month from the input date and uses the DateSerial function to create a new date. You give the DateSerial function a year, a month, and a day, and it gives you back the appropriate date. The StartOfMonth function ignores the day portion of the input date and always uses 1 as the day instead. If for some reason the input date can’t be interpreted as a date, the function returns the special value Empty. The Empty value is the value used when a variable has never been initialized. The Visual Basic Date function in the UserForm_Initialize procedure always returns a valid date, so if you call only the StartOfMonth function from the UserForm_Initialize procedure, it doesn’t have to handle an invalid date. But whenever you write a custom function, you should write it to work in a variety of possible situations. Returning an Empty value when the argument is an invalid date is one way to make your function more flexible.
If you want to test a function from a form, you can do so from the Immediate window. Because the function is part of the code for a form object, you must include the form name before the function name. For example, you could test the StartOfMonth function in the Immediate window by entering the following statement in the Immediate window: ?frmPrint.StartOfMonth("May 23, 2007").
Change the statement in the UserForm_Initialize procedure to txtMonth.Value = StartOfMonth(Date).
Press F5 to run the dialog box, check the date in the month box, and then close the form.
The date should be the first day of the current month.
Many controls need to be initialized. Some controls, such as the option buttons, can be initialized when you create the form. Other controls, such as the month text box, need to be initialized when you run the form, and the Initialize event handler is the place to initialize controls when you run the form.
Your form allows you to specify what both the rows and columns of the report should look like. You still need a way to start printing. To do that, you add a command button. In theory, you don’t need a cancel button because you can always just click the Close Window button to close the form. But a cancel button is easier to understand and use, and that’s the whole purpose of a good user interface.
Activate the Form window.
Click the CommandButton button in the Toolbox, and then click to the right of the Rows frame on the form.
Press and hold the Ctrl key, and drag the new button down to make a copy of it.
Clicking the top button will print the report, but clicking the bottom one won’t. Clicking either button will close the form.
Change the caption of the top button to Print, change the name to btnPrint and change the Default property to True.
Only one command button on a form can be the default. A default button is the one that is “clicked” when you press the Enter key.
Change the caption of the bottom button to Cancel, change the name to btnCancel and change the Cancel property to True.
Only one command button on a form can be a cancel button. A cancel button is the one that is “clicked” when you press the Esc key. Normally when you click a cancel button, you expect the form to close. A cancel button by itself, however, doesn’t close the form. First you have to add an event handler to it.
Double-click the cancel button to create an event handler named btnCancel_Click and enter the statement Unload Me as the body of the procedure.
The Unload command removes a form from memory. The Me keyword refers to the current form. The macro statement Unload Me therefore removes from memory the form that contains the control whose event handler is currently running.
Select btnPrint from the Objects list at the top of the code window to create a new procedure called btnPrint_Click and enter these two statements as the body of the procedure:
Unload Me MsgBox "Printing"
The first statement removes the form, and the second statement is a placeholder until you add the functionality to print the report.
Save the workbook, and run the form several times. Try clicking the Cancel and Print buttons. Try pressing the Esc or Enter keys.
Pressing the Enter key or clicking the Print button should display the Printing message. Pressing the Esc key or clicking the Cancel button should make the form disappear quietly.
Some people prefer to use the Tab key on the keyboard rather than the mouse to move the focus from control to control. For them, you should make sure that the tab order is logical.
Run the form one more time. Select the Start with month check box to enable the month text box. Press the Tab key repeatedly. Watch the small gray box move from control to control.
The gray box identifies the control that has the focus.
Click Cancel to close the form.
For this form, the tab order should be optAll, optSummary, optDetail, chkMonth, txtMonth, btnPrint, and btnCancel. If that’s not the tab order for your controls, the Visual Basic editor provides a simple way to change the tab order.
Click the background of the form. Then on the View menu, click Tab Order.
The Tab Order dialog box shows five controls: grpRows, chkMonth, txtMonth, btnPrint, and btnCancel. It treats the grpRows frame control (along with the controls it contains) as a single item. If a control is out of place in the sequence, you simply select the control and click the Move Up or Move Down button to put it in the right place.
After making any necessary adjustments, click OK to close the dialog box.
When you have nested controls-such as the option buttons inside the frame-you must set the tab order for the nested controls separately.
Select the frame box (or any of the option buttons), and on the View menu, click Tab Order again.
This time, the Tab Order dialog box shows only the controls inside the frame.
If the controls are not in the order optAll, optSummary, and optDetail, make any necessary adjustments, and then click OK to close the dialog box.
Save the workbook.
The tab order is easy to set, but remember that you need to set the order for the controls in each frame separately.