How to Control Multiple Cells with Spin Button in Excel

Using the Spin Button in Microsoft Excel can help you quickly and easily make adjustments to numerical values in a worksheet. The Spin Button is a small, rectangular control with two arrows pointing in opposite directions. When you click the Spin Button, it adds or subtracts a value from the active cell. The amount of the adjustment can be determined according to the user’s choice.  In this article, I am going to explain the whole procedure on the topic of how to control multiple cells with a Spin Button in Excel. I hope it will be helpful for you if you are looking for a similar sort of thing.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Step-by-Step Procedures to Control Multiple Cells with Spin Button in Excel

To control multiple cells with Spin Button in Excel, I am going to explain the whole procedure in detail in the following section. I have divided the whole procedure into 4 different parts to make it more simple to understand. Let’s march forward.

Step 1: Create Dataset with Proper Parameters

• It is a must to have a proper dataset to explain the whole procedure of controlling multiple cells with Spin Button. So, I have arranged a dataset where the monthly sales of a store are listed. I have arranged the data of that store in the Item No, Item Name, Price, and Quantity I am going to calculate the total sales amount of each product considering the unit price fixed. So, the only variable here is the Quantity column. So, my purpose is to control the cells from E5 to E9 with the Spin Button.

• In order to find the total sales amount of a product, it is necessary to multiply the unit price of a product by the quantity of that product. For that purpose, input the following formula in a preferred cell (i.e. F5) to have the total sales amount of a product.

• Now, press ENTER to have the result.

• Afterward, use Fill Handle to AutoFill the rest cells in column F.

Step 2: Generate Spin Button

• To generate a Spin Button, go to the Developer tab first.
• Next, click on Insert from the ribbon.
• Pick Spin Button from the ActiveX Controls group.

• Now, draw the Spin Button in a preferred place with a suitable dimension.

Step 3: Define Specification from Properties

• To define specified attributes, right-click on the mouse keeping the cursor on the Spin Button.
• From the available options, go to Properties.

• Now, set the properties according to your need.
• Here, I have defined the Name as SpinButtonMultipleCells, and set the background color and font color from the BackColor and ForeColor options. I have also limited the values between 0 to 100 and changed values by 1 from the SmallChange option.

• Now, we will have the Spin Button with the specific properties.

Step 4: Apply VBA to Define Selection Change

• We need to define the cells which we want to control with the Spin Button. For this purpose, go to the Design Mode option from the Developer tab.
• Now, double-click on the Spin Button.

• After that, select Worksheet from the Object section and SelectionChange from the Procedure
• Followingly, input the following VBA code.

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(ActiveCell, Worksheets("SpinButtonAdd").Range("E5:E9")) Is Nothing Then
SpinButtonMultipleCells.LinkedCell = ActiveCell.Address
Else
SpinButtonMultipleCells.LinkedCell = ""
End If
End Sub

• Finally, we have our desired output. Select a cell from the defined ones and click on the Spin Button to increase or decrease the value. Look at the below  GIF  to understand clearly.

Read More: How to Create Spin Button with VBA in Excel (3 Useful Methods)

Conclusion

At the end of this article, I would like to add that I have tried to explain the whole procedure on the topic of how to control multiple cells with Spin Button in Excel. It will be a matter of great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below. You can visit our site for more articles about using Excel.

Related Articles

• [Solved!] Excel Spin Button Not Working (2 Reasons with Solutions)
• How to Rotate Spin Button in Excel (With Easy Steps)
• Using Spin Button to Increment Decimal in Excel (2 Easy Ways)
• How to Use Spin Button Percentage in Excel (3 Easy Examples)
• How to Use Spin Button to Display Negative Values in Excel