| 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. For 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. Project Explorer -
In the Visual Basic Editor, click the Project Explorer toolbar button, double-click the frmPrint form, and then close the Project window. When you have a project with several components, the Project window is often the easiest way to get to the right place. -
Double-click the Print button to show the btnPrint_Click event handler procedure. -
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. | Tip | 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! -
Save the workbook, and press F8 to run the form. (Press F8 repeatedly to step through the initialization procedures.) 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. If you press F8 to run the form, you can step through any event handler procedures triggered by controls on the form. -
Click the Summary option, and then click Print. Press F8 repeatedly to step through the btnPrint_Click procedure. Close the message box as necessary. An option button 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. Also, if you were to add a fourth view option, all you'd have to do is define a new view on the worksheet and add an option button with the appropriate caption to the form. You wouldn'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 by assigning to a variable either the date from the month box or an invalid value. -
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. -
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. -
Save the workbook, and press F5 to run the form. Select the check box, type 9/1/2002 in the month box, and click Print. The worksheet now shows only the months starting from September. -
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. What if you type 4/15/2002 as the date? Ideally, the macro should convert 4/15/2002 to the appropriate 4/1/2002. Look in the library of useful functions you've created during this project and see whether you can find one to convert a date to the start of the month. -
Double-click the Print button. 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 prescient of you to write the StartOfMonth function to handle invalid dates.) If the myMonth variable contains the Empty value, you'll want to show a message and make the value easy to fix. -
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 text box. 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. -
Save the workbook, and then press F5 to run the form. Try enabling the month, typing Dog, and clicking Print. Try typing Jun 23, 02 and clicking Print. (Close the message box.) When you put an edit box onto 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 make the report display the report in print preview mode, you can then decide whether to print it or just admire it. -
Double-click the Print button. In the btnPrint_Click procedure, replace MsgBox "Printing" with ActiveSheet.PrintPreview. After the report prints, you should restore the rows and columns in the worksheet. -
After the statement ActiveSheet.PrintPreview, type the statement ShowView "All". -
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. -
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 create a standard macro that displays the form. Once you've done that, you can make event procedures that automatically add a menu command when the workbook opens and remove the command when the workbook closes. Project Explorer -
Click the Project Explorer button to show the Project window, double-click the Module1 module (that is, the module that contains the ShowView and HideMonths macros), activate the code window, and scroll to the bottom of the module. -
Insert this macro: Sub ShowForm() 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. | Tip | 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. | -
In the Project window, double-click the ThisWorkbook object. Select Workbook from the Objects list, and insert these statements as the body of the Workbook_Open procedure: Dim myButton As CommandBarButton Set myButton = _ Application.CommandBars("Worksheet Menu Bar").Controls.Add myButton.Caption = "&Print Report" myButton.Style = msoButtonCaption myButton.BeginGroup = True myButton.OnAction = "ShowForm" The CommandBars collection works like any other collection; you specify an item using the name of the item. The Controls collection also works like other collections; you add an item-in this case, a command bar button-using the Add method. The Add method returns a reference to the new object, which you can assign to an object variable. Unless you specify otherwise, the Add method adds the control to the end of the collection. Assigning a value to the Caption property sets the text for the command. Assigning msoButtonCaption to the Style property makes the control display the caption rather than an icon. The BeginGroup property adds a line before the command, separating it from the built-in commands. The OnAction property is the name of the macro you want to have run. This menu item will appear on the main Excel menu bar whenever you open the workbook. -
From the Procedures list at the top of the code window, select BeforeClose. Insert these statements as the body of the Workbook_BeforeClose procedure: ActiveWorkbook.Save On Error Resume Next Application.CommandBars("Worksheet Menu Bar") _ .Controls("Print Report").Delete The first statement saves the workbook. This prevents Excel from asking whether to save the workbook. The second statement keeps the macro from displaying an error if the Print Report command doesn't exist for some reason. The third statement deletes the new Print Report menu command. These statements will execute whenever the workbook closes, removing any trace of the Print Report command. -
Switch to Excel, and then save and close the workbook. -
Open the Chapter11 workbook and allow Excel to enable macros. The new Print Report command appears on the main worksheet menu as soon as you open the workbook. -
Click the new Print Report menu command, and then click Cancel. -
Close the workbook. The new command disappears. Your custom form is completely integrated with Excel. Creating a fully usable form entails 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, go and create dozens more for your own projects. |