1. Download the Budget.xls workbook from the URL for our class: If you are working in the Wright 112 lab during a hands-on class, follow instructions on whiteboard for where to save it. http://www.cns.uni.edu/~jacobson/c022.html 2. Using the Visual Basic toolbar, click the Visual Basic editor button. 3. The 2nd button from the left on Visual Basic's Standard toolbar will be the Insert UserForm button. It has other choices too. Click the UserForm option and a new, empty user form will be displayed. 4. Use the View menu or else the Properties Window button from the Standard Toolbar, to display the Properties Window. Set the Caption property to Printing Options Set the (Name) property to frmPrint. 5. Adding some radio buttons, also known as option buttons. a. Click the Form window, so it is active. b. Then go to the Control toolbox, click the Frame button to choose that toolbox tool. Now click near the top-left corner of the form. c. Double-click on the OptionButton control in the Control toolbox. Click in 3 places on the form, but inside of the Frame control. This creates three different radio button controls. Click the OptionButton button in the toolbox again, to turn it off. (Double-click to use it many times, single click to turn it off. This works with Format Painter too). d. Activate the Properties window again and select Frame1 from the list of choices given by the drop-down list at the top. (Alternatively, you can just click on the Object that you want to view and/or set some properties for. We are setting properties at Design time here, not Run time). e. Set the Caption property to Rows and set eh (Name) property to grpRows. (The grp prefix stands for "group". Note that when we use a prefix, the 4th letter of the (Name) is always capitalized. ------ f. Set the properties of the option buttons as follows, from the top one downward to the bottom one. (Name) Caption Accelerator Value ------ ------- ----------- ----- optAll All A True optSummary Summary S optDetail Detail D g. Select all three option buttons by clicking between the bottom of the entire frame and the bottom option button (optDetail) and dragging a rectangle across the three so that it touches all three of the option buttons. When you let up the mouse, all three will be selected. h. Format menu, Vertical Spacing submenu, Remove command. Format menu, Align submenu, Lefts command. Format menu, Size to Fit command. Now drag the group of three radio buttons up to the left hand corner of the frame, and then go ahead and resize that frame to just fit around the option buttons. i. Save the workbook. Click the F5 button to see what your UserForm looks like running. Try the option buttons out. 6. Add a checkbox control from the Control toolbox. Add the checkbox just below the Frame control and over toward the left side of the form. Set the properties for the checkbox control to: Caption (Name) Accelerator ------- ------ ----------- Start with month chkMonth m 7. Add a TextBox control to your UserForm so that the GUI (graphical user interface) will allow the application user to specify any month, if they need to have the report start with a month other than the current month. Place the TextBox just to the right of the chkMonth CheckBox oontrol's caption. Set the properties to: (Name) = txtMonth Value = 11/1/2005 Enabled = False 8. Double-click the chkMonth check box control. The event handler for the chkMonth will be created: Private Sub chkMonth_Click() txtMonth.Enabled = chkMonth.Value <--- add this line of VBA code End Sub 9. Save the workbook again. Press F5 function key to run the form, and click the CheckBox a few times to see the chkMonth_Click() event handler in action. Note that the Value of a CheckBox is Boolean, i.e. is True or False. The .Enabled property is also Boolean. When .Enabled is False, you TextBox or OptionButton or CommandButton is said to be disabled. 10. Double-click on the background of the UserForm to add an event handler for the form. You will see UserForm_Click() as the Private Sub and that Click() event is NOT the one we want. From the drop-down list, choose Initialize as the Event we want. Enter the following one statement into the UserForm_Initialize(). Private Sub UserForm_Initialize() txtMonth.Value = Date <---- Date function used... End Sub Now press F5 to run the form and see the effects of the initializing of the TextBox control with today's date. 11. Do to the View menu, Immediate Window command. (Or Control+G) In the Immediate Window, type the following four commands: ?Day(Date) <-------- Press Enter after each command.... ?Month(Date) ?Year(Date) ?Date Your input and output will look something like this: ?Day(Date) 19 ?Month(Date) 10 ?Year(Date) 2006 ?Date 10/19/2006 11. Add a custom Function (not a Sub) to convert dates to the start of the month, to the first day of the month. Below the UserForm_Initialize() Private Sub, add this function: Function StartOfMonth(InputDate) If IsDate(InputDate) Then StartOfMonth = DateSerial(Year(InputDate), Month(InputDate), 1) Else StartOfMonth = Empty End If End Function As you can see above, VBA does not require you to specify the input TYPE of the arguments, or the RETURN TYPE of the function. 13. Change the statement that is in the UserForm_Initialize() Sub to the following: txtMonth.Value = StartOfMonth(Date) F5 run the form and note the effect of invoking the function. 14. Add one command button to the form. Place it to the right of the Frame and toward the top of the form. Hold down the CTRL (Control) key and drag the new button down to make an identical sized copy of it. (Copy and Paste shortcut). Set the properties of the two buttons as follows: (Name) Caption Accelerator Default Cancel ------ ------- ----------- ------- ------ btnPrint Print P True btnCancel Cancel True 14. Double-click the Cancel button, and add the statement to the event handler, so it looks like this: Private Sub btnCancel_Click() Unload Me End Sub 15. Double-click the Print button and code it to become: Private Sub btnPrint_Click() Unload Me MsgBox "Printing on " & Date End Sub 16. Try out the Cancel and Print buttons by F5 running the Form. Try out the Enter key and the Esc (Escape) key, to see if Enter is default for Print button and Esc is another way to execute the btnCancel_Click() event's code. Try the Tab key for the running Form. What controls first has the focus? What is the order of the controls for the sequence when you do the Tab key enough times to cycle through all the controls that can receive the focus? Be sure to Enable the TextBox control (for the date entry), to see when it gets the focus too. 17. How do you set the tab order for the controls? Click anywhere on the background of the Form (at Design time). Do the View menu, Tab Order command. Move the controls up or down as needed until you have the following order: grpRows, chkMonth, txtMonth, btnPrint, txtCancel After clicking OK to establish that new Tab Order, click anywhere on the frame box or on an option button. Do View menu, Tab Order command again. The Tab Order adjustments may not be needed. Give the controls the order: optAll, optSummary, optDetail ------------------------------------------------------------------------ 18. You have the Create custom views on a worksheet as a separate photocopied handout. We did part of this during the hands-on class on Friday, October 20th. Everything we did is there on the handout. (See separate handout for All, Summary and Detail views, and the Dynamically hide columns material). ------------------------------------------------------------------------ ********************* ****************** --Implementing the Form-- ******************************* ********************* 19. In Visual Basic, click the Project Explorer button, double-click the frmPrint Form icon to get to that component of the project. 20. Double-click the Print button to show the btnPrint_Click() event handler procedure. Insert the following statements at the very beginning of the btnPrint_Click() procedure: Dim myOption As Control Dim myView For Each myOption in grpRows.Controls If myOption.Value = True Then myView = myOption.Caption End If Next myOption ShowView myView How nice it was the Caption property of each OptionButton is exactly the same as the name of one of the Views that we created in Step #18 (from the separate handout for Friday 13th lab). 21. Save the workbook. Staying in VBA code window or the form window where you see the GUI frmPrint, press F8 to run the form. Try the option button for getting the Summary, then click the Print button. Press F8 to single step through the event handler code, statement by statement. Finish execution of the Form. Note that you can run it with either F5 or F8 when you see its GUI or its code window, i.e. when it is active. 22. Double-click the Print button again. Inside of the Print procedure, you will add the following statements right after the Dim myView line: Dim myMonth If chkMonth.Value = True Then myMonth = txtMonth.Value Else myMonth = "no date or bad date" End If 23. Now insert the statement, HideMonths myMonth right AFTER the statement ------------------ ShowView myView You have to change the view BEFORE you do the hiding of the months, because showing a custom view will automatically redisplay any hidden columns. Here is where the ORDER of statements is important in an algorithm. ----- 24. Now, after saving the workbook, go ahead and test out the new functionality you have added to the frmPrint by pressing the F5 key to run it. Try looking at only the months from June on by typing 6/1/2005 or 6/1/1997 into the textbox for the month. Try 9/1/2005 (or 1997). 25. Checking for errors in the edit box. What if the user types 6/21/1997 or 6/21/2005, or perhaps 6/1/07 or 6/1/05 (the yy instead of yyyy format is NOT acceptable). We want input like 6/21/05 to be automatically converted to 6/1/2005, for example. In the btnPrint_Click() procedure, replace myMonth = txtMonth.Value with myMonth = StartOfMonth( txtMonth.Value ) If the TextBox txtMonth contains a date, our function StartOfMonth() will return the properly formatted date in the mm/1/yyyy format. Otherwise, it will return the Empty value. Suppose it returns an Empty value: If myMonth = Empty Then MsgBox "Month is Invalid" txtMonth.SetFocus txtMonth.SelStart = 0 txtMonth.SelLength = 100 Exit Sub End If Save your workbook again. Press F5 to run the form and try the following with the month enabled: UNI Jun 23, 97 (or Jun 23, 05) 6/21/2005 (or 6/21/1997) 26. Now, the final change to be made to the btnPrint_Click() code is to replace the MsgBox "Printing" statement with ActiveSheet.PrintPreview ActiveSheet is an object, PrintPreview is a method. ----------- ------------ After the new statement ActiveSheet.PrintPreview put the statement: ShowView "All" Save your changes and test out the various views, with and without limiting the start month to just see some of the months. 27. Finally, how do we launch the frmPrint Form? Click the Project Explorer toolbar button to show the Project window. Double-click the Module1 module, to get back to the Module that contains our ShowView and HideMonths macros. Insert this macro somewhere in Module1 code window: Sub ShowForm() frmPrint.Show <------- frmPrint is the (Name) of the End Sub UserForm object we created, if you set the .Name property at Design time. 28. Now, how do we execute the ShowForm() macro? In the Project Explorer window, you can see the ThisWorkbook object as one of the items in the project. Double-click the ThisWorkbook object icon. From the Object list, choose Workbook, and from the procedures or Events list, choose Open. Type the following statements into the Workbook_Open() event procedure: Private Sub Workbook_Open() Dim myButton As CommandBarButton Set myButton = _ Application.CommandBars("Worksheet Menu Bar").Controls.Add myButton.Caption = "&Print Report" myButton.Style = msoButtonCaption <------ mso prefix for ??? myButton.BeginGroup = True micro soft office myButton.OnAction = "ShowForm" - - - End Sub 29. In the same window as 28, pull down the Procedures list for the Workbook object again. Select the BeforeClose procedure. The template that VBA inserts should look like this: Private Sub Workbook_BeforeClose(Cancel As Boolean) End Sub The statements that go inside that will make it look like this: Private Sub Workbook_BeforeClose(Cancel As Boolean) ActiveWorkbook.Save On Error Resume Next Application.CommandBars("Worksheet Menu Bar") _ .Controls("Print Report").Delete End Sub 30. Now, switch to Excel and save and then close the workbook. Open up the workbook again. Does it have the new command as the last choice on the menu bar, after the Help menu? Close the workbook. Does the new command disappear from the Excel menu bar? The .Delete method just before the End Sub in 29 just above here causes it to be removed during the Workbook_BeforeClose() event.