Button (CommandButton) with Excel VBA

The CommandButton control is mainly used to initiate the execution of certain actions triggered by pressing the button—for example, starting or stopping a program, printing results, and so on.
Thus, the primary event associated with the button is the Click event.
The primary property of the button is the Caption property, which returns and sets the text displayed on the button’s surface.

Button Menu

Let’s consider a business situation of creating a button menu. The workbook contains three worksheets. On the first sheet, there are two buttons with the names of the two other sheets. Clicking a button activates the corresponding worksheet. When clicking the second button, not only is the sheet activated, but the sheet also scrolls so that the specified cell is displayed in the top-left corner of the sheet window.

So, create a workbook with Sheet1, Sheet2, and Sheet3. On Sheet1, place two buttons.

Using the Properties window, assign their property values as shown in next Table.

Table. Property values set in the Properties window

In the module of Sheet1, type the required code. The project is ready. The activation of the sheet is done, of course, by the Activate method of the Worksheet object. Scrolling the sheet so that a specific cell (in this case, T30) is displayed in the top-left corner of the sheet window is accomplished with the ScrollColumn and ScrollRow properties of the Window object.

NOTE
To scroll the screen so that the desired range is displayed, you can also use the Show method of the Range object. For example:

Cells(30, 20).Show

Navigation Using Hyperlinks

Navigation through the workbook can certainly be implemented using a button menu, but it can also be done in the classical way—without writing code—by means of hyperlinks.

In a workbook with three sheets (Sheet1, Sheet2, and Sheet3):

• Select Sheet1.
• In cell B1, type Sheet2.
• Click Insert Hyperlink in the Links group on the Insert tab of the ribbon.
• In the Insert Hyperlink dialog box, set the necessary parameters. The options Existing File or Web Page, Place in This Document, Create New Document, and Email Address indicate the destination of the hyperlink. The Text field sets the tooltip text. The Cell Reference field specifies the destination cell. The Or select a place in this document field provides a list of sheets and named ranges.
• In the dialog box, select Place in This Document, set Cell Reference to A1, then select Sheet2. Click OK.
• In cell B2, type Sheet3. Click Insert Hyperlink again. The Insert Hyperlink dialog box will appear.

• Select Place in This Document. In Cell Reference, enter T30. In the list, choose Sheet3. Click OK.

Button Scenario

Buttons allow not only navigation but also entering data into cells, thereby creating button-driven scenarios—for example, displaying the total sum of expenses or profits under different combinations of components.

As an example, let’s find the total sum a + b + c of three variables under two different sets of values shown in Table 4.6.

Table. Variable values

Steps:

• On the worksheet, assign cells B2, B3, B4 for variables a, b, c.
• In cell B5, enter the formula: =SUM(B2:B4).
• In the range D3:D5, enter the first set of values (3, 4, 5).
• In the range E3:E5, enter the second set of values (2, 3, 4).
• Create two buttons and assign property values using the Properties window as shown in next Table.
• In the module of Sheet1, type the required code (see file 4-Button Scenario.xlsm).

Table. Property values set in the Properties window

Clicking Variant 1 reads values from D3:D5 and writes them into B2:B4.
Clicking Variant 2 reads values from E3:E5 and writes them into B2:B4.

The button scenario project is ready.

Button Scenario with Pictures and Custom Mouse Pointers

Buttons can be made more visually appealing by embedding pictures. Additionally, changing the mouse pointer displayed over the button (for example, from an arrow to a pointing hand) adds visual attractiveness.

The picture is loaded onto the button using the Picture property.
The PicturePosition property sets the layout of text and picture.
The MousePointer property sets the type of mouse pointer.

Table. Valid values of the MousePointer property

Constant	Value	Description
fmMousePointerDefault	00	Default
fmMousePointerArrow	01
fmMousePointerCross	02
fmMousePointerIBeam	03
fmMousePointerSizeNESW	06
fmMousePointerSizeNS	07
fmMousePointerSizeNWSE	08
fmMousePointerSizeWE	09
fmMousePointerUpArrow	10
fmMousePointerHourglass	11
fmMousePointerNoDrop	12
fmMousePointerAppStarting	13
fmMousePointerHelp	14
fmMousePointerSizeAll	15
fmMousePointerCustom	99	User-defined

If MousePointer = fmMousePointerCustom, the cursor is based on a .cur file, referenced by the MouseIcon property. In VBA, Picture and MouseIcon values are set using the LoadPicture function with the file path as a parameter.

Example: Button Scenario for Entering Formulas

Now, instead of numbers, we’ll input formulas into worksheet cells for calculations—specifically x + y and x – y .

Steps:

• On the worksheet, assign cells B1 and B2 for variables x and y. Cell B3 will hold the formula entered from code.
  • Either =B1+B2
  • Or =B1-B2
• Create two buttons and assign properties as shown in next Table.

Table . Property values set in the Properties window

Clicking Sum enters the formula =B1+B2 in cell B3 and writes Sum in A3.
Clicking Difference enters the formula =B1-B2 in B3 and writes Difference in A3.

Button Scenario for Entering Formulas

Private Sub cmdSum_Click()
    Range("B3").Formula = "=B1+B2"
    Range("A3").Value = "Sum"
End Sub

Private Sub cmdSub_Click()
    Range("B3").Formula = "=B1-B2"
    Range("A3").Value = "Difference"
End Sub

Interactive Button and Determining the Average Sales Volume

By handling the MouseDown and MouseUp events, you can make a button more interactive. We will demonstrate this with a simple application. Suppose we have some sales data for the company “Almateus,” which exports various rubber-technical products. To present the data more clearly in the sales volume table, you need to highlight in red the row with the maximum sales volume, and in yellow the rows where the sales volume is above average. The sales data constantly change, so you must create an application that automates the process of coloring the required rows.

To implement this example, perform the following steps.

• On the worksheet, allocate the range B2:B7 for sales volumes.
• In cell B8, enter the formula to calculate total sales: =SUM(B2:B7).
• Create a button and, using the Properties window, set its property values as shown in next Table.
• In the worksheet module Sheet1, type the code.

Table 4.10. Property values set in the Properties window

Pressing the Refresh button will trigger the recalculation and reformatting of the table.

The procedures handling the MouseDown and MouseUp events program different appearances of the button—namely the style, size, and color of the font (Font.Bold, Font.Size, and the button’s ForeColor), the background color (BackColor), and the rendering of a shadow (Shadow). Changing these button properties creates the effect of additional interactivity.

The table recalculation is performed in the DoRefresh procedure. The maximum and average values are obtained using worksheet functions encapsulated in the WorksheetFunction object. Cell color is set by the Interior.Color property. Fill removal is implemented by setting ColorIndex to xlColorIndexNone.

Exchanging Values Between Two Selected Cells

As a final example of working with buttons on a worksheet, consider a project that can swap values between any two selected cells. On the worksheet, create a button and, for example, enter the words Fire, Air, Water, Earth in cells A2, B22, H17, J3. In the worksheet module Sheet1, type the code (see file 7-Value Exchange.xlsm on the CD) that handles the button’s Click event. That’s all—now select any two of these cells and click the button, and their values will be swapped automatically. In addition, our program validates the selected area: it must consist of exactly two cells.