Creating a Form's User Interface

The Budget worksheet shows detailed budget information for the year 2002. It includes both detail and summary rows.

click to expand

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 custom dialog box, or user form. Here's the strategy for creating the form:

  • Design what the form will look like. The way the form looks and acts, called the user interface, is the first thing that the user sees, and it suggests how to use the form. The easiest way to design a form in Microsoft Visual Basic for Applications is to just jump in and create it.

  • Create the macros you need to make the form work. These are the procedures that interact with Excel objects. The tasks that the form executes are called its functionality. Adding functionality might involve making changes to the worksheet that 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 implementation.

The process of designing the form's user interface can help you figure out what functionality you need to develop.

Create the Form

Visual Basic Editor

  1. With the Chapter11 workbook open, click the Visual Basic Editor button.

    The second button from the left on the Standard toolbar in the Visual Basic Editor allows you to insert a new object type. The object type inserted by the button changes to whatever you most recently used it for. The default is for the button to insert a UserForm.

    Insert UserForm

  2. Click the arrow next to the Insert UserForm button to display a list of objects that you can insert.

  3. Click the UserForm option to create a new, empty user form.

    The button might have a different picture depending on whether you've previously used it.

    click to expand

    The form is stored in your project just as a module is. You can 'run' the form from Visual Basic for Applications in the same way that you run a macro.

    click to expand


  4. 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. The second form you create will be captioned UserForm2, and so on. The caption is a property; you can change the caption using the Properties window.

    click to expand

    Properties Window

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

    click to expand

  6. Change the value of the (Name) property to frmPrint. (The Name property has parentheses around it, which cause it to 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, you might want to put something a little fancier inside it.

Add Option Buttons

In this exercise, you want the user to be able to 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 Visual Basic for Applications displayed the user form, it automatically displayed the Toolbox for forms. This Toolbox is similar to the Control Toolbox you use to add ActiveX controls to a worksheet.


  1. Click the Form window. If you don't see the Toolbox, click the Toolbox button on the Standard toolbar to display it.

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


  3. 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 be exact in where you position the controls.

    click to expand

  4. Activate the Properties window, and select Frame1 from the drop-down list at the top.

  5. Type Rows as the value of the Caption property, and type grpRows as the value of the Name property. (The prefix grp- is short for group, which is an old name for a frame. The prefix frm- is commonly used as the prefix for a form.)

  6. From the drop-down list, select OptionButton1. Type All as the value of the Caption property, optAll as the value of the Name property, and A as the value of the Accelerator property. (You've probably guessed what the prefix opt- is short for.) 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.


    In the Toolbox, you can use the keyboard to jump to a new property by pressing Ctrl+Shift and the first letter of the property name. For example, to select the Accelerator property, press Ctrl+Shift+A.

  7. Give the second option button the caption Summary, the name optSummary, and the accelerator key S.

  8. Give the third option button the caption Detail, the name optDetail, and the accelerator key D.

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

  10. On the Format menu in the Visual Basic Editor, click Vertical Spacing, and then click Remove. From the Format menu, click Align and then click Lefts. Again, from the Format menu, click Size To Fit. Finally drag the group of controls up close to upper left corner of the frame, and resize the frame just to fit around the option buttons.

    click to expand

    The Format menu provides powerful tools for getting the controls on a form to line up properly.

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

    click to expand

A frame with a set of option buttons is a good user interface for selecting a single option from a predefined list.

Add a Check Box with a Related Text Box

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.

Also, even though the budget analyst says that the report should start with the current month, you know that exceptions inevitably will arise. You should therefore add a text box that lets you specify a different start month, just to be prepared.


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

    click to expand

  2. In the Properties window, change the caption for the check box to Start with month, change the name to chkMonth, and change the Accelerator to m.

    You must use a lowercase letter because the letter in the caption is lowercase. The accelerator character must match the character in the caption exactly.

  3. Double-click the right size handle of the check box selection rectangle to shrink the rectangle to fit the caption.

    click to expand

    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.


  4. Click the TextBox button in the toolbox, and then click to the right of the check box caption.

    click to expand

  5. Change the text box name to txtMonth, set the Value property to 7/1/2002, 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.

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

    click to expand

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

  8. Save the workbook, press F5 to run the form, and click the check box a couple of times. Then close the form.

    click to expand

  9. When the check box is cleared, you can't change the date. When it's selected, you can.

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 Visual Basic for Applications code, it is still part of the user interface of the form, not part of its functionality.

Initialize the Text Box

When you created the month text box, you assigned 7/1/2002 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.

  1. Double-click the background of the form. A new procedure named UserForm_Click appears.

    click to expand

    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 you don't want to wait until the user clicks the form to initialize the month. You therefore need a different event.

  2. From the Procedures list, select the Initialize event. After the UserForm_Initialize procedure appears, delete the UserForm_Click procedure.

  3. Enter the following statement as the body of the procedure:

    txtMonth.Value = Date

    click to expand

    Date is a built-in Visual Basic function that returns the current date, based on your computer's internal clock.

  4. Press F5 to run the form.

    click to expand

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

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

  6. 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 same 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 the function, you can do so from the Immediate window. Because this function is part of the code for a form object, however, you must include the form name before the function name. For example, you could test the function in the Immediate window by entering the following statement: ?frmPrint.StartOfMonth("May 23, 2002").

  7. Change the statement in the UserForm_Initialize procedure to txtmonth.Value = StartOfMonth(Date).

  8. Press F5 to run the dialog box, check the date in the month box, and close the form.

    The date should be the first day of the current month.

    click to expand

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 accomplish that task.

Add Command Buttons

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 the whole purpose of a good user interface is to make the form easy to understand and use.

  1. Activate the Form window.


  2. Click the CommandButton button in the Toolbox, and then click on the form, to the right of the Rows frame.

  3. Press and hold the Ctrl key, and drag the new button down to make a copy of it.

    The top button will print the report, and the bottom one will not. Either button will close the form.

    click to expand

  4. Change the caption on the top button to Print, assign P as the accelerator key, 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 gets 'clicked' when you press the Enter key.

  5. Change the caption on the bottom button to Cancel, don't assign an accelerator key, change the name to btnCancel, and change the Cancel property to True.

    click to expand

    Only one command button on a form can be a cancel button. A cancel button is the one that gets '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.

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

    click to expand

    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.

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

    click to expand

    The first statement removes the form, and the second statement is a placeholder until you add the functionality to print the report.

  8. Save the workbook, and run the form several times. Try clicking the Cancel and Print buttons. Try pressing the Esc or Enter keys.

    click to expand

    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.

Set the Tab Order for Controls

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

    click to expand

    The gray box identifies the control that has the focus. When you use the keyboard, you can press the Tab key to move the focus from control to control.

  2. Click Cancel to close the form.

    Some people prefer to use the keyboard rather than a mouse. For them, you should make sure that accelerator keys are properly defined and that the tab order is logical. 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.

  3. Click the background of the form. From the View menu, click the Tab Order command.

    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.


    If you don't see a Tab Order command, move the mouse over the arrow at the bottom of the View menu.

    click to expand

  4. After making any necessary adjustments, click OK to close the dialog box. Select the frame box (or any of the option buttons), and from the View menu, click the Tab Order command again.

    This time, the Tab Order dialog box shows only the controls inside the frame.

    click to expand

  5. After making any necessary adjustments, 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.

Microsoft Excel 2002 Visual Basic for Applications Step by Step
FrameMaker 6: Beyond the Basics
ISBN: 735613591
EAN: 2147483647
Year: 2005
Pages: 122
Authors: Lisa Jahred

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net