Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.

Excel Spin Button Multiple Cells

  • 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.
=D5*E5
  • Now, press ENTER to have the result.

Create Dataset with Proper Parameters


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.

Generate Spin Button

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

 Excel Spin Button Multiple Cells


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.

Define Specification from 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.

Excel Spin Button Multiple Cells


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.

Apply VBA to Define Selection Change

  • 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.

Excel Spin Button Multiple Cells

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

Naimul Hasan Arif

Naimul Hasan Arif

Hello, I am Arif. I am an Engineer who graduated from BUET. I want to pursue an enterprising career in a progressive environment where my skills & knowledge can be enhanced to their maximum potential to contribute to the overall success and growth of the organization.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo