Creating Custom Command Buttons | Microsoft Excel 2002 Visual Basic for Applications Step by Step (Step by Step (Microsoft))

You might feel perfectly comfortable running a macro by pressing a shortcut key combination or even pressing F5 in the Microsoft Visual Basic editor. But if you’re going to give a macro to somebody else, you want to make it as easy to run as possible. In Chapter 1, “Make a Macro Do Simple Tasks,” you learned two ways of making macros easy to run: by assigning a shortcut key to a macro and by assigning a macro to a button on the Quick Access Toolbar. However, most people won’t know the shortcut key, and sometimes you want something more noticeable than a button on the Quick Access Toolbar.

In this section, you’ll learn how to launch a macro from a command button on a work-sheet. This creates an easy and obvious way run a macro with just a simple click. Buta command button can also respond to other actions. For example, you can make a macro run by simply moving the mouse over the button. Because a command button can respond to a complex set of events, it requires a whole new way of running a macro. This new approach uses what are called event handler procedures. Event handler procedures are special macros that are linked to an object such as a command button. Before you create command buttons, first set up some simple sample macros that the buttons can execute.

Try the ZoomIn and ZoomOut Macros

Each worksheet window in Excel has a Zoom level. You can change the level interactively.

Drag the Zoom control at the lower-right corner of the Excel window left and right to see the Zoom effect. When you’re finished, leave the zoom at 100%.

The Events text file contains two simple macros, ZoomIn and ZoomOut, that do the same thing as the Zoom control. They are macros that you’ll run by using various events. Here are the macros:
```
Sub ZoomIn()   Dim myZoom As Integer   myZoom = ActiveWindow.Zoom + 10   If myZoom <= 400 Then   ActiveWindow.Zoom = myZoom   End If End Sub Sub ZoomOut()   Dim myZoom As Integer   myZoom = ActiveWindow.Zoom - 10   If myZoom >= 10 Then   ActiveWindow.Zoom = myZoom   End If  End Sub 
```
Each macro retrieves the current value for the Zoom property of the active window. If it can change the value and stay within the acceptable limits (between 10 and 400, corresponding to the zoom percent limits in Excel), it changes the property; otherwise it does nothing.
Copy the two macros from the Events text file, and paste them into a new module in the macro-enabled Chapter09 workbook.
On the Visual Basic toolbar, click in the ZoomIn macro, and press F5 a few times to see how the worksheet zooms in. Step through the macro to become comfortable with how it works.
Click in the ZoomOut macro. Press F5 as necessary to return the window to the normal zoom level.

These are “typical” macros. In this chapter, you’ll learn new ways to run them. The first technique you’ll learn is to run them by using command buttons.

Enable the Developer Tab in the Ribbon

As you know, the Ribbon has a special tab just for Developers. You can write, run, and edit macros just fine without using the Developer tab, but you need the Developer tab to add custom controls such as command buttons to a worksheet. If you haven’t already done so, here’s how to display the Developer tab.

Click the Microsoft Office Button, and click Excel Options.

Microsoft Office Button
On the Popular page, select the Show Developer tab in the Ribbon check box.
Click OK to close the Excel Options dialog box, and then click the Developer tab.

The Code group of the tab contains commands for creating macros; all of these commands are also readily available in other places. The XML group is not related to macros at all. The Controls group has what you need for adding a command button to a worksheet.

Create a Custom Command Button

A command button is useful for running macros that relate to a specific worksheet. Command buttons are usually large and easy to click, with a label describing what the button does.

The Insert list on the Developer tab contains a number of controls that you can use on a worksheet or form. These controls are called ActiveX controls. These ActiveX controls are a special kind of drawing object that can carry out an action when you click them. The ActiveX control we’ll work with in this chapter is the Command Button control.

Link a Command Button to a Macro

You don’t assign a macro to a command button. Instead, you create a macro witha special name, in a special place, and the macro automatically links to the button. Fortunately, the Control group of the Developer tab has a button that will do all the work of naming the macro for you.

With the command button still selected, click the View Code button.

The Visual Basic editor window appears with a new macro. The word Private before the macro name means that this macro won’t appear in the Run Macro dialog box. The macro name is btnZoomIn_Click. The name is important. The part of the name that precedes the underscore matches the name of the command button. The part of the name that follows the underscore matches the name of the event that this macro will handle. In this example, the macro runs whenever you click the button. A macro linked to an event like this is called an event handler.

Important

The word procedure is a technical synonym for a macro. Excel uses the word macro because macro recorder is less intimidating than procedure recorder. In general, this book uses macro to refer to the procedures that you can run from the Macro dialog box and procedure to refer to functions and event handlers.

You could copy the code from the ZoomIn macro into the btnZoomIn_Click procedure, but it’s easier simply to run that macro (since it already exists) from this one.

Type ZoomIn as the body of the procedure.

The final procedure should look like this:
```
Private Sub btnZoomIn_Click()   ZoomIn  End Sub 
```
You’re now ready to try clicking the button.
Switch back to Excel, click in any cell in the worksheet to deselect the button, and then click the button.

The procedure doesn’t run. You simply reselected the button. You need some way of letting Excel know whether clicking an ActiveX control should run the event handler or simply select the control. You do that by controlling design mode. When Excel is in design mode, clicking a control selects it. When Excel isn’t in design mode, it’s in run mode, and clicking a control runs the event handler procedure.

Whenever you put an ActiveX control on a worksheet, Excel automatically switches to design mode. When Excel is in design mode, the Design Mode button on the Developer tab is highlighted.
Click the Design Mode button to turn off design mode.

The selection handles disappear from the command button.
To try the Zoom In button, click it.
Click the Design Mode button to turn design mode back on, and click the command button.

The selection handles appear on the command button, but the code does not run.

ActiveX Controls and Forms Controls

The Insert button on the Developer tab includes two different sets of controls: ActiveX controls and Forms controls. As you can tell by looking at the buttons, the sets are almost identical. So what is the difference? Forms controls are simpler. They have a very limited number of properties and don’t have event handlers. You can think of Forms controls as belonging to Excel directly, and ActiveX controls as belonging to Visual Basic. Forms controls are not affected by the Design Mode button. To select an existing Forms control, you hold down the Ctrl key as you click the control.

In this book, you learn how to use ActiveX controls for two reasons. First, to create a custom dialog box, you use ActiveX controls. Second, because Forms controls are simpler, if you learn how to create an ActiveX control, you can easily transfer the knowledge to a Forms control. If all you want from a control is the ability to run a simple macro, a Forms control may be better. If you want more “control” over the control, an ActiveX control may be better. If you need to create an application that will work with different character sets-for example, Arabic or Chinese-there isa significant difference between the two types of control. The Forms controls are actually part of Excel, and because Excel supports Unicode, Forms controls can display captions in any language supported by your operating system and fonts. But the ActiveX controls are part of Visual Basic for Applications, and because VBA supports code pages for localization, ActiveX controls can support only the language of the current code page. If the terms code page and Unicode don’t mean anything to you, then you should be able to use either ActiveX controls or Forms controls.

Create an Event Handler on Your Own

Although you can create a handler for the click event for a control by just clicking the View Code button, you might find it enlightening to see how you can also create an event handler directly in the Visual Basic editor.

To create a Zoom Out command button over cells A3 and A4, drag a rectangle on the worksheet from the upper-left corner of cell A3 to the lower-right corner of cell B4. In the Properties window, assign the name btnZoomOut and the caption Zoom Out, and set the TakeFocusOnClick property to False. Don’t click the View Code button.

See Also
For detailed instructions, see steps 2 through 6 of the section titled “Create a Custom Command Button.”
On the Developer tab of the Ribbon, click the Design Mode button to turn off design mode.

You can create the event handler in Visual Basic even if design mode is turned off.
Switch to the Visual Basic editor, click anywhere in the btnZoomIn_Click procedure, and look at the bar just above the code.

Above the code portion of the window are two boxes. The box on the left contains the first half of the procedure name (btnZoomIn), and the box on the right contains the second half of the procedure name (Click). These two boxes are named Object and Procedure, respectively.
Click the Object arrow.

The list shows all the objects related to the current worksheet that can have event handlers. In this case, they are btnZoomIn, btnZoomOut, and Worksheet.
Select btnZoomOut from the list.

A new procedure appears. Click is the default event for a button, so the new procedure is named btnZoomOut_Click, which is precisely what you need.
Type ZoomOut as the body of the procedure.

Switch to Excel, and try both buttons.

The lists at the top of the code window can help you build event handlers by combining an object name with an event name.

Tip

You can also create an event handler simply by typing the correct name for the macro. As long as you spell the object name and the event name properly, the event handler will work. The object and procedure lists just help you avoid errors.

Make a Button Respond to Mouse Movements

The command button can recognize several different events. Three of the most useful events are a click (the Click event), a double-click (the DblClick event), and a mouse movement (the MouseMove event). The MouseMove event is especially fun to write an event handler for because it has arguments you can use to control the way the procedure works.

In the Visual Basic editor, select btnZoomOut from the Object list, and then select MouseMove from the Procedure list.

The declaration for the new procedure is relatively long. This is what it looks like when divided into shorter lines:
```
Private Sub btnZoomOut_MouseMove( _   ByVal Button As Integer, _   ByVal Shift As Integer, _   ByVal X As Single, _   ByVal Y As Single) 
```
This event handler procedure has four arguments: Button, Shift, X, and Y. (The word byVal means that Excel will ignore any changes the procedure makes to an argument.) The arguments communicate information that you can take advantage of as you write the macro. The Button argument indicates whether a mouse button is down as the mouse moves. The Shift argument indicates whether the Shift, Ctrl, or Alt key is pressed. The X and Y arguments indicate the horizontal and vertical position of the mouse.
Insert ZoomOut as the body of the new procedure, switch to Excel, and move the mouse over the Zoom Out button. (You don’t even have to click. Just moving the mouse over the button causes the procedure to run. Events can happen quickly.)

You can use the arguments that the MouseMove event provides to control the procedure. The most useful argument is Shift. Specifically, Shift equals 1if the Shift key is down, 2 if the Ctrl key is down, and 4 if the Alt key is down. If multiple keys are down, the argument equals the sum. Thus if the argument value is 3, both the Shift and the Ctrl keys are down. By creating an If block based on the Shift value, you can change the procedure so that it zooms in when the Shift key is down, and out when the Ctrl key is down-and does nothing otherwise.
Replace the body of the btnZoomOut_MouseMove procedure with these statements:
```
If Shift = 1 Then   ZoomIn ElseIf Shift = 2 Then   ZoomOut  End If 
```
Tip
The ElseIf keyword allows you to combine Else and If statements into a single statement.
Switch to Excel, and try the event handler. Try moving the mouse by itself. Then try pressing and holding the Shift key as you move the mouse. Then try pressing and holding the Ctrl key as you move the mouse.

As you move the mouse over the button, you can practically see the procedure running over and over. Each time the button detects the mouse moving, it triggers another event and the event handler procedure runs again. Event handler procedures can be a powerful way to make things happen.

Explore the Visual Basic Project

You might wonder where all these event handlers are stored and how they relate tothe macros that you create with the macro recorder. When you use the macro recorder to create a macro, the macro is stored in a module. You can have multiple macros in a single module, and you can have multiple modules in a workbook. (Each time you close and reopen a workbook, the macro recorder creates a new module for any new macros you record.) Event handler procedures for a command button are attached to the work-sheet that contains that button. Visual Basic refers to all the code in a single workbook- whether the code is in a module or attached to a worksheet-as a project. The Visual Basic editor has a special window that allows you to explore the project. In the Excel file, the Visual Basic project is stored separately from everything else in the workbook. This makes it easy for Excel to refuse to open a workbook that contains macros unless the file is explicitly saved as a macro-enabled workbook.

In the Visual Basic editor, click the Project Explorer button.

Project Explorer

The Project window appears. This is a dockable window, so you can move it to the edge of the Visual Basic editor or make it float. In the window, you see that the name of the project is VBAProject. The name of the workbook (Chapter09.xlsm) appears in parentheses. Procedures can be stored either in ordinary modules (grouped under the Modules heading in the Project window) or attached to workbooks and work-sheets (grouped under Microsoft Excel Objects in the Project window).

Associated with each worksheet is a page that contains any code for that worksheet or for objects on that worksheet. When you create a new worksheet, a new code page appears in the Project window. When you delete a worksheet, the worksheet’s code page disappears.
Double-click the entry labeled Module1.

The main Visual Basic editor window displays the macros stored in Module1.
Double-click the entry labeled Sheet1.

The main Visual Basic editor window displays the event handlers for the objects on Sheet1.
In Excel, drag the Sheet1 tab to the right, and then press and hold the Ctrl key as you release the mouse button.

Excel creates a copy of the sheet. The copy’s name is Sheet1 (2), and it has its own copy of the command buttons.
Switch to the Visual Basic editor, and look at the Project window.

There’s a new sheet in the list under Microsoft Excel Objects.

The name in parentheses, Sheet1 (2), matches the name on the worksheet tab. The name in front of the parentheses, Sheet4, is a unique name that Visual Basic generates. (Now that you have a sheet with Sheet4 as the internal name, if you use Insert Worksheet to create a new Sheet4, Visual Basic will give it Sheet5 as its internal name!) You can use the Properties window to change the internal name of the sheet.
Click the Properties button, and in the Properties window, change the value of the (Name) property to MyNewSheet.

Properties
Double-click the MyNewSheet worksheet item in the Project window.

The main Visual Basic editor window now shows the event handler procedures for the copies of the command buttons. These procedures look just like the procedures that are linked to the command buttons on Sheet1, but they’re separate copies. Even if you change the btnZoomIn_Click procedure on MyNewSheet, the btnZoomIn_Click procedure on Sheet1 remains unchanged.
In Excel, delete the Sheet1 (2) worksheet. Then switch back to the Visual Basic editor, and look at the Project window.

As you probably anticipated, the entry for MyNewSheet has disappeared, along with the procedures that were associated with it.

Important

When you delete a worksheet that has event handler procedures associated with it, all the procedures are destroyed with the worksheet. Save your work frequently when you write event handlers so that you can recover your work if you accidentally delete a worksheet.