Implementing a Form

You’ve created a user interface that allows you to specify which rows and columns to print. You’ve also created the functionality for the form. The ShowView and HideMonths macros show the appropriate rows and columns. You now need to make the user interface drive the functionality. You need to implement the form.

On this form, the Print button is what formats and prints the report. You’ll put all the code that links the form to the functionality into the btnPrint_Click procedure.

Implement Option Buttons

To implement the option buttons, you need a way to determine which option button value is True. The frame control has a Controls property that returns a collection of all the controls in the frame. You can loop through those controls and determine which option button value is True.

  1. In the Visual Basic editor, click the Project Explorer toolbar button, double-click the frmPrint form, and then close the Project window.

    image from book
    Project Explorer

    image from book

    When you have a project with several components, the Project window is often the easiest way to get to the right place.

  2. Double-click the Print button to show the btnPrint_Click event handler procedure.

    See Also 

    For details about using a For Each loop, see the section titled “Loop Through a Collection by Using a For Each Loop” in Chapter 7, “Control Visual Basic.”

  3. Insert these statements at the beginning of the procedure, before the Unload Me statement:

    Dim myControl As Control Dim myView  For Each myControl In grpRows.Controls   If myControl.Value = True Then   myView = myControl.Caption   End If Next myControl ShowView myView 

    This For Each loop inspects each control in the frame, looking for a value of True. You declare the loop variable as a Control (not as an OptionButton) because it’s possible for a frame to contain other types of controls besides option buttons.


    If you loop through the controls of a frame that contains controls other than option buttons, you should check to see whether the control is an option button. You can use the conditional expression TypeName(myControl) = "OptionButton" to determine whether the control is an option button.

    The loop stores the caption of the selected option in a variable, and the macro later uses that variable as the argument when it runs the ShowView macro. What a fortuitous coincidence that you used the same names for the custom views as for the captions of the option buttons!

  4. Save the workbook, and press F8 to run the form. Then press F8 repeatedly to step through the initialization procedures until the form appears.

    If you press F8 to run the form, you can step through any event handler procedures triggered by controls on the form.


    Make sure you have the form’s code window active when you press F8. You can run the form by pressing F5 while either the form design window or the form code window is active, but the form code window must be active to use F8.

  5. In the form, click the Summary option, and then click Print.

  6. Press F8 repeatedly to step through the btnPrint_Click procedure.

  7. Close the message box as necessary.

Option buttons can be easy to implement if you plan ahead. In this example, giving the custom views in the worksheet the same names as the captions of the option buttons made the option buttons easy to implement. If you want to add a fourth view option, all you have to do is define a new view on the worksheet and add an option button with the appropriate caption to the form. You don’t need to make any changes to any of the procedures.

Implement a Check Box

If the check box is selected, the Print button event handler should run the HideMonths macro. Actually, the HideMonths macro will do nothing if you give it a date that it doesn’t find. You can take advantage of that fact by assigning to a variable either the date from the month box or an invalid value.

  1. In the form, double-click the Print button to show the btnPrint_Click procedure, and add the following statements after Dim myView.

    Dim myMonth   If chkMonth.Value = True Then   myMonth = txtMonth.Value Else   myMonth = "no date"  End If 

    These statements assign to the myMonth variable either the value from the month text box or an obviously invalid value.

  2. Insert the statement HideMonths myMonth after the statement ShowView myView.

    You place this statement after the ShowView statement because you want to change the view before hiding the months; showing the custom view redisplays all the hidden columns.

  3. Save the workbook, and press F5 to run the form.

  4. Select the check box, type 9/1/2007 in the month box, and click Print.

    The worksheet now shows only the months starting from September.

    image from book

  5. Click OK to close the message box.

Check for Errors in an Edit Box

What if you run the form and type Dog as the date? The macro shouldn’t hide any columns, but it should point out the error. Also, what if you type 4/15/2007 as the date? Ideally, the macro should convert 4/15/2007 to the first day of the month: 4/1/2007. Think about all the functions you’ve created during this project and see whether you can find one that can convert an arbitrary date to the start date of the month.

  1. Double-click the Print button.

  2. In the btnPrint_Click procedure, replace myMonth = txtMonth.Value with myMonth = StartOfMonth(txtMonth.Value).

    The StartOfMonth function converts a date to the first of the month. If the input date isn’t a valid date, the function returns the Empty value. (That was remarkably forward-looking of you to write the StartOfMonth function in such a way that it can handle invalid dates.) Once the StartOfMonth function finishes its job, if the myMonth variable contains the Empty value, you’ll want to show a message and make the value easy to fix.

  3. Insert these statements before the Else statement:

    If myMonth = Empty Then   MsgBox "Invalid Month"   txtMonth.SetFocus   txtMonth.SelStart = 0   txtMonth.SelLength = 1000   Exit Sub End If 

    When you run the form, the macro appropriately displays a message box explaining the problem if you type an invalid date. After you close the message box, you should be able to just start typing a corrected value. For that to happen, however, the macro must move to the text box and preselect the current, invalid contents.

    The SetFocus method moves the focus to the text box. Setting the SelStart property to 0 (zero) starts text selection from the very beginning of the textbox. Setting the SelLength property to 1000 extends text selection to however much text there is in the box. Using an arbitrarily large value such as 1000 simply avoids having to calculate the actual length of the contents of the box.

  4. Save the workbook, and then press F5 to run the form.

  5. Enabling the month, type Dog, and click Print.

  6. Click OK to close the error.

  7. Type Jun 23, 07 and click Print.

  8. Click OK to close the Printing placeholder message box.

When you put an edit box on a form, you must think about what the macro should do if the user enters an invalid value. In many cases, displaying an error message and preselecting the invalid entry is the best strategy. The SetFocus method and the SelStart and SelLength properties are the tools that allow you to implement that strategy.

Print the Report

The Print form now does everything it needs to do-everything, that is, except print. If you display the report in print preview mode, you can then decide whether to print it or just admire it.

  1. Double-click the Print button.

  2. In the btnPrint_Click procedure, replace MsgBox "Printing" with ActiveSheet.PrintPreview.

    After the report is printed, you should restore the rows and columns in the worksheet.

  3. After the statement ActiveSheet.PrintPreview, type the statement ShowView "All".

    The finished Print button event handler routine should look like this:

    Private Sub btnPrint_Click()   Dim myControl As Control   Dim myView   Dim myMonth      If chkMonth.Value = True Then   myMonth = StartOfMonth(txtMonth.Value)   If myMonth = Empty Then   MsgBox "Invalid Month"   txtMonth.SetFocus   txtMonth.SelStart = 0   txtMonth.SelLength = 1000   Exit Sub   End If   Else   myMonth = "no date"   End If      For Each myControl In grpRows.Controls   If myControl.Value = True Then   myView = myControl.Caption   End If   Next myControl      ShowView myView   HideMonths myMonth      Unload Me   ActiveSheet.PrintPreview   ShowView "All"  End Sub
  4. Save the workbook, press F5 to run the form, select the Summary option, limit the months to August and later, click the Print button, and then click Zoom to see the beautiful report.

    image from book

  5. Close the Print Preview window.

The user interface of the form is now linked to its full functionality. All that’s left is to provide a way for the user to run the form from Excel instead of from Visual Basic.

Launch the Form

To launch the form, you can add an unobtrusive button to the worksheet. The top-left corner of the Budget worksheet is always visible (because the panes are frozen), so you could put a button in the right side of cell B1. Rather than use an ActiveX button, you can use an ActiveX label so that you can make the background transparent and set the font color.

  1. In Excel, activate the Developer tab of the Ribbon, click the Insert arrow, and click Label (ActiveX Control).

    image from book
    Label (ActiveX Control)

  2. Drag a small rectangle in the right half of cell B1.

  3. Click the Properties button on the Ribbon, and set the following property values: Change (Name) to lblPrint, change BackStyle to 0 – fmBackStyleTransparent, change Caption to Print, change ForeColor to a blue color, change PrintObject to False, and change TextAlign to 2 – fmTextAlignCenter.

    image from book

  4. Right-click the label, and click View Code.

  5. In the lblPrint_Click event handler, insert the statement frmPrint.Show.

    The procedure will look like this:

    Private Sub lblPrint_Click()   frmPrint.Show End Sub 

    The Show method of a form displays the form. To refer to the form, simply use the name that you gave it when you created it.


    Ordinarily, you can’t activate a worksheet while a form is displayed. When a form prevents you from selecting the worksheet, it’s called a modal form. If you want the user to be able to use the worksheet while the form is displayed, add the argument False after the Show method. This creates what is called a nonmodal form.

  6. Switch to Excel, turn off design mode, save the workbook, and then click the Print label.

  7. When the Print Options dialog box appears, click Cancel.


    Instead of putting an ActiveX control on the worksheet, you could also create an ordinary macro that shows the form, and then add that macro to the Quick Access Toolbar for this workbook. Some users may not notice the Quick Access Toolbar icon, so a label may be preferable provided a pane is frozen and will always be visible.

    Creating a fully usable form involves three major steps: creating the user interface, creating the functionality, and joining them together into a working tool. Now that you’ve created one form, you can create dozens more for your own projects.

    CLOSE the Chapter11.xlsm workbook.

Microsoft Office Excel 2007 Visual Basic for Applications Step by Step
Microsoft Excel 2002 Visual Basic for Applications Step by Step (Step by Step (Microsoft))
ISBN: 0735613591
EAN: 2147483647
Year: 2004
Pages: 99
Authors: Reed Jacobsen © 2008-2017.
If you may any questions please contact us: