METBD 050 – VBA Example – Cmd Button – F03.doc Page 1 of 2 11/5/01, Revised 11/10/03 METBD 050 VBA Command Button Example Example: 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 and A6 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 any values 4. 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 in the place where you want the command button. At this point, make the button as big as you wish. This creates a command button object called “CommandButton1.” 6. Right-click on the command button and select “Properties” form the shortcut menu. The window shown in Figure 2 will appear in the worksheet. Set the following properties for the object: a. Caption: Compute V & A b. Height: 26.25 c. Left: 143.25 d. PrintObject: True e. Top: 7.5 f. Width: 91.5 7. Close the properties window when you have set the properties. To access the properties again, right-click on the object and select “Properties”. 8. Right-click on the command button object again and select “View Code.” The Visual Basic Editor opens and the window shown in Figure 3 is created. 9. In the code window, type the code as shown in Figure 4. Be careful, typos may make the code not function properly. 10. Close the VB Editor and return to Excel. 11. Turn off the “Design Mode” button on the VB toolbar to activate the command button. 12. ‘Click’ the command button to execute the code. Caption: the words seen on the command button Height: the height of the command button Left: the position of the left side of the object with respect to the left side of the screen Top: the position of the top of the object with respect to the top of the screen PrintObject: true/false to print object with the worksheet Width: the width of the command button Figure 2: CommandButton1 Properties Window Command button Figure 1: Control ToolboxMETBD 050 – VBA Example – Cmd Button – F03.doc Page 2 of 2 11/5/01, Revised 11/10/03 Private 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 Sub Figure 4: The Code Shows 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...