METBD 050VBA Command Button ExampleExample: Write the visual basic code to compute the volume and surface area of a block provided that the side lengths are typed into Excel as shown in cells B1:B3 below. The code should execute by pressing the command button labeled “Compute V & A.” The code should automatically display the results in cells A5 andA6 as shown.Procedure:1. Open a new Excel workbook. Save it as “Example 2.xls” on your P: drive.2. Type text to identify the values in cells B1:B3.3. Type in values for w, h, and d in cells B1:B3. You should be able to use anyvalues4. From the Visual Basic toolbar, open the Control Toolbox. See Figure 1.5. Click the Command button tool to turn it on. On the worksheet, click and drag inthe place where you want the command button. At this point, make the button asbig as you wish. This creates a command button object called“CommandButton1.”6. Right-click on the command button and select “Properties” form the shortcutmenu. The window shown in Figure 2 will appear in the worksheet. Set thefollowing properties for the object:a. Caption: Compute V & Ab. Height: 26.25c. Left: 143.25d. PrintObject: Truee. Top: 7.5f. Width: 91.57. Close the properties window whenyou have set the properties. Toaccess the properties again, right-click on the object and select“Properties”.8. Right-click on the command buttonobject again and select “View Code.” The Visual Basic Editor opens andthe window shown in Figure 3 iscreated.9. In the code window, type the code asshown in Figure 4. Be careful, typosmay make the code not functionproperly.10. Close the VB Editor and return toExcel. 11. Turn off the “Design Mode” button onthe VB toolbar to activate thecommand button.12. ‘Click’ the command button to execute the code.METBD 050 – VBA Example – Cmd Button – F03.doc Page 1 of 211/5/01, Revised 11/10/03Caption: the words seen on the command buttonHeight: the height of the command buttonLeft: the position of the left side of the object with respect to the left side of the screenTop: the position of the top of the object with respect to the top of the screenPrintObject: true/false to print object with the worksheetWidth: the width of the command buttonFigure 2: CommandButton1 Properties WindowCommand buttonFigure 1: Control ToolboxMETBD 050 – VBA Example – Cmd Button – F03.doc Page 2 of 211/5/01, Revised 11/10/03Private Sub CommandButton1_Click()'' Declare variables as double'Dim w As Double, h As Double, d As Double, volume As Double, sa As Double'' Get box side values' w = Range("B1").Value 'sets the variable w equal to value of cell B1 h = Range("B2").Value 'sets the variable h equal to value of cell B2 d = Range("B3").Value 'sets the variable d equal to value of cell B3'' Compute volume'Volume = w * h * d'' Compute surface area (sa)'sa = 2 * w * h + 2 * w * d + 2 * h * d'' Place results in cells A5 and A6'Range("A5").Value = "The volume is " & volume & " cu. in."Range("A6").Value = "The surface area is " & sa & " sq. in."‘‘ Select the active worksheet‘ActiveSheet.Select‘End SubFigure 4: The CodeShows the object to which the code applies.Shows the event that triggers the exectuion of the code.The code for the command button will go here.Figure 3: Code Window for
View Full Document
Unlocking...