Furman University



Exploring Getting Started with VBA, Chapter 2Scripted LectureObjects and FormsJoe (of Joe’s Classic Cars) has expanded into the loan business. He wants you to create an Excel application that will show a form for the user to fill in and then show the loan amortization schedule for the amount the user wants to borrow.STEP 1 INSERT A FRAME CONTROLOpen exp2010_vba02_script_JoesLoans .xlsm file (click the Enable Content button if needed), and then save it as exp2010_vba02_h1_JoesLoans_Solution.Click the Developer tab, and then click Visual Basic in the Code group. Double-click Forms in the Project Explorer window if necessary, and then double-click frmLoan to display the Loan Preview form.Click Frame in the Toolbox, move to the form and draw a frame on the form below the frame holding the radio buttons, by holding down the left mouse button and dragging the frame control. The frame should be approximately 122 pixels wide by 522 pixels high. You can check the size in the property panel.Select Frame1 in the Name property box in the properties window, and then type fmeLoanInfo.Select the text in the Caption property box in the Properties window, and then type Loan Information:Save the form.STEP 2 ADD LABEL AND TEXTBOX CONTROLSClick the border of the Loan Information frame in the form to ensure that it is selected.Click Label in the Toolbox, and then click below the Loan Information caption in the frame.Change the Caption to Loan Amount:, and then change the TextAlign to 3 – fmTextAlignRight.Press and hold Ctrl, select the label you just created, and then drag down to create a copy below the original label.Change the Caption to Interest Rate:Click the Loan Information frame to select it, click TextBox in the Toolbox, and then click to the right of the Loan Amount label.Change the Name property to txtAmt, and then set the TabStop to True, TabIndex to 0, and ControlSource property value to F4.Insert a Label control below the TextBox control; change the Name property to lblRateValue; and then delete the default Caption, set the BackColor property to &H00FFFFC0& (Row 1 Column 6 on the color palette, set the BorderStyle property to 1 – frmBorderStyleSingle, and TextAlign property to 2 – frmTextAlignCenter.Select the label with the Loan Amount caption; Press and hold Ctrl, and then drag to the right to create a copy. Change the Caption to Term(Years):.STEP 3 INSERT LISTBOX, CHECKBOX, AND BUTTON CONTROLSSelect the outer border of the Loan Information frame in the form.Click ListBox in the Toolbox, and then click to the right of the Term (Years) label. Set the Name property to lstTerm, BorderStyle to 0 – fmBorderStyleNone, ColumnWidths to 0.15, Height to 60, TabStop to True, TabIndex to 1, and TextAlign to 2 – fmTextAlignCenter.Click CheckBox in the Toolbox, and then click below the Interest Rate label. Set the Name Property to chkPreCustomer, Caption property to I have borrowed from Joe’s before, TabStop to True, TabIndex to 2, and Width to 150.Click the form instead of the frame, click CommandButton in the Toolbox, and then click in the bottom-right corner of the form.Change the Name property to cmdClose, change the Caption property to Close, and then change the Width property to 60. Adjust the location of the button so that the right edge of the button is aligned with the right edge of the frame.Save the form. Keep the workbook onscreen if you plan to continue with Hands-On Exercise 2. If not, close the workbook, and then exit Excel.Functions and FormsSTEP 1 CREATE A FUNCTION PROCEDURE AND CALLING PROCEDUREOpen the macro-enabled exp2010_vba02_h1_JoesLoans_Solution workbook if you closed it at the end of Hands-On Exercise 1, save it as exp2010_vba02_h2_JoesLoans_Solution, and then display the VBA window.Right-click the frmLoan form in the Project Explorer, and then select View Code.Type Option Explicit and then press Enter twice.Use Code Window 1 to create the CalcInterestRate procedure. Remember to type a lowercase l not the number 1 in lstTerm.Text. Also remember to tab in the statements within the If...ElseIf statement block for readability. Code Window 1 Calculate Interest SubroutineUse Code Window 2 to create the calling procedures.Code Window 2 Calling CalcInterestRate SubSave the code then close the VBA window.STEP 2 INSERT A COMMAND BUTTON ON A WORKSHEETRight-click the Instructions sheet tab, and then select Unprotect Sheet.Click the Developer tab, Click Design Mode in the Controls group, if necessary, to activate Design mode, then click Insert in the Controls group.Click Command Button (ActiveX Control) on the palette. Drag to create a command button to approximate the size and location shown in Figure 13.Click Properties in the Controls group.Set the Name property to cmdDisplayForm, and then set the Caption property to Enter/Edit Customer Information. Make sure that the Enabled property is True so that the command button can react to a user clicking it and that the button displays the entire caption. Adjust the size if necessary.Close the Properties window, and then save the workbook.Figure 1 Command Button PlacementSTEP 3 ADD CODE PROCEDURE TO A CONTROLClick Design Mode in the Controls group, if necessary, to activate Design mode.Right-click the Enter/Edit Customer Information command button on the worksheet, and then select View Code.Type the code shown in Code Window 3.Code Window 3Command Button Click Event CodeClick Save on the toolbar, and then close the VBA window.Click Design Mode in the Controls group on the Developer tab in Excel to deactivate Design mode.Click the Enter/Edit Customer Information button to view the form. Click the Close button in the top-right corner of the form to close it. Save the workbook.STEP 4 ADD CODE PROCEDURE TO CLOSE A FORMOpen the VBA window, and then display the frmData form.Right-click the Close button, and then select View Code.Type the code shown in Code Window 4 between the Sub and End Sub statement. The code in this window also includes the code for the list box click event. Be sure to enter it.Code Window 4 Close Button and Term Listbox CodeSave the code, and then minimize the VBA window.Click the Loan Information sheet tab if necessary. Click Insert in the Controls group, and then click Command Button (ActiveX Control).Drag to create a command button control to the right side of the Joe’s Loans heading on the left side of the worksheet.Click Properties in the Controls group. Set the Name property to cmdReturn and the Caption property to Return to Instructions.Close the Properties window, right-click the Return to Instructions button, and then select View Code.Press Enter, press Tab, type Worksheets ("Instructions"). Activate, and then press Enter.Save and close the VBA window.Click Design Mode in the Controls group to deactivate Design mode.Click the Instructions sheet tab to make it the active sheet. Click the Enter/Edit Customer Information button you created earlier. When the form opens, enter the information shown in Figure 2 then click Close in the bottom-right corner.Figure SEQ Figure \* ARABIC 2 Completed FormClick the Return to Instructions button you created at the top of the Customer Information worksheet.Save the workbook. Keep the workbook onscreen if you plan to continue with Hands-OnExercise 3. If not, close the workbook, and then exit Excel.Loan Payment ScheduleSTEP 1 CREATE CODE TO VIEW THE LOAN PAYMENT SCHEDULEOpen the exp2010_vba02_h2_JoesLoans_Solution macro-enabled workbook if you closed it after Hands-On Exercise 2, and then save it as exp2010_vba02_h3_JoesLoans_Solution. Click the Instructions sheet tab if necessary, click the Developer tab, and then click Design Mode in the Controls group. Click the Enter/Edit Customer Information command button to select it, press and hold the left mouse button, press and hold Ctrl, and then drag to create a copy of the command button within the second border.Make sure the second command button is selected. Click Properties in the Controls group. Type cmdCreateSchedule in the Name property box, and then type View the Loan Payment Schedule in the Caption property box. Close the Properties window, and then click Save on the toolbar.Right-click the View the Loan Payment Schedule command button, and then select View Code.Enter the code shown in Code Window 5.Code Window SEQ Code_Window \* ARABIC 5Make sure the insertion point is at the end of the intStart = 10 statement that you just typed. Press Enter, and then type the code shown in Code Window 6.Code Window SEQ Code_Window \* ARABIC 6Save the code, close the VBA window, and then click Design Mode in the Controls group to deactivate Design mode.Click the View Loan Payment Schedule command button you just created. Figure 3 shows the results.Figure SEQ Figure \* ARABIC 3STEP 2 CREATE CODE TO ACTIVATE THE INSTRUCTIONS WORKSHEETRight-click the Schedule sheet tab, and then select Unprotect Sheet.Click Insert in the Controls group, and then click Command Button (ActiveX Control)Drag to create a command button to the right of the Joe’s Loans heading in the top-left corner.Click Properties in the Controls group. Type cmdReturn in the Name property box, and then type Return to Instructions in the Caption property box. Adjust the height and width to be similar to the button you created on the Loan Information worksheet in Hands-On Exercise 2.Close the Properties window.Right-click the Return to Instructions button, and then select View Code.Press Enter, press Tab, type Worksheets(“Instructions”).Activate and then press Enter.Save and close VBA. Click Design Mode in the Controls group to deactivate it.Right-click the Schedule sheet tab, select Protect Sheet, and then click OK in the Protect Sheet dialog box. Click the Return to Instructions button you just created on the Payment Schedule worksheet to return to the Instructions worksheet. Save the workbook.STEP 3 ADD FORMULAS TO THE DISCLOSURE WORKSHEETRight-click the TIL Info sheet tab, and then select Unprotect Sheet.Set the values of the cells as shown in the table below.Cell on TIL Info SheetFormulaB4=NOW()B6='Loan Information'!B4B7='Loan Information'!F11A11='Loan Information'!F6C11='Loan Information'!F4D11=B11+C11A15='Loan Information'!F8*12B15=Schedule!C6D15=B4+15D16=DATE(YEAR(D15),MONTH(D15)+A15,DAY(D15))B11 =-CUMIPMT(A11/12,A15,C11,1,A15,0)Repeat steps 2b–g to insert a command button on the TIL Info worksheet.Right-click the TIL Info sheet tab, select Protect Sheet, and then click OK in the Protect Sheet dialog box.Click the Return to Instructions button you just created on the TIL Info worksheet to return to the Instructions worksheet. Save the workbook.STEP 4 ADD A BUTTON TO DISPLAY THE DISCLOSURE WORKSHEET Click Design Mode in the Controls group on the Developer tab. Copy the View Loan Payment Schedule button, and then paste the duplicate button within the third border directly below the first two command buttons.Click Properties in the Controls group. Type cmdTIL in the Name property box, and then type View the Truth-in-Lending Disclosure in the Caption property box. Close the Properties window, and then click Save on the toolbar.Right-click the View the Truth-in-Lending Disclosure command button, and then select View Code.Press Enter, press Tab, type Worksheets("TIL Info").Activate and then press Enter. Close the VBA window.Click Design Mode in the Controls group to deactivate it.Right-click the Instructions sheet tab, select Protect Sheet, and then click OK in the Protect Sheet dialog box. Save the workbook.Click the View the Truth-in-Lending Disclosure command button you just created to ensure it works, and then click the Return to Instructions button in the Disclosure worksheet to return to the Instructions worksheet.Click each button on each worksheet to test the functionality of each button again. If errors exist, view the code, identify the error, save the code, and then click the buttons again.Save and close the workbook. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download