Add 2 spinners for a single cell?

venshi

New Member
Joined
Sep 4, 2017
Messages
7
Hi, would like to ask if anyone knows how to add 2 spinners for a single cell? For example, the first spinner would be increasing the value by 1 while the other spinner would increase the value in the cell by 0.1. Thus if I want to increase the cell by 20.5%, I would just have to click the first spinner twice and the second spinner 5 times. May any geniuses here please enlighten me? Thanks!!!!
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,787
Office Version
  1. 2010
Platform
  1. Windows
Use two spinners for two cells, and use a formula in a third cell to combine the other two.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,651
Office Version
  1. 2013
Platform
  1. Windows
You could do it this way with just one spin button.
Click the up button increases Range("A1") by 1
Click the down button increase Range("A1") by 0.1
Code:
Private Sub SpinButton1_SpinDown()
Cells(1, 1).Value = Cells(1, 1).Value + 0.1
End Sub
Private Sub SpinButton1_SpinUp()
Cells(1, 1).Value = Cells(1, 1).Value + 1
End Sub
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,787
Office Version
  1. 2010
Platform
  1. Windows
How do you make it go down?
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,651
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

How do you make it go down?

I do not see where he wants it to go down.
His quote:
"the first spinner would be increasing the value by 1 while the other spinner would increase the value in the cell by 0.1.
 

venshi

New Member
Joined
Sep 4, 2017
Messages
7
sorry for the confusion if any! I would also like to be able to make the value go up and down. So how do I use a formula in the third cell to join the 2??
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115

ADVERTISEMENT

Make spinner 1 drive cell A1.
Make spinner 2 drive cell A2.
In A3 use
=A1+A2
 

venshi

New Member
Joined
Sep 4, 2017
Messages
7
Yup just tried that and it works. Woohoo! However, one flaw of it that is the second spinner cannot influence the first spinner. For example, if my first spinner (A1) is set by incremental of 1% and my second spinner (A2) is set at 0.1%. When I click the first spinner twice upwards the cell (A3) will turn into 2%. However, I am unable to use to reduce the value of 2% by 0.1 to 1.9% using the second spinner because my set cell link for the second spinner is still actually zero. Any tips on solving this issue?
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,651
Office Version
  1. 2013
Platform
  1. Windows
Try this:
Needs no formula:

Up button increases by 1
Up button increases by 0.1

Down button decrease by 1
Down button decreases by 0.1




Code:
Private Sub SpinButton1_SpinDown()
'Goes down by 0.1
Cells(1, 1).Value = Cells(1, 1).Value - 0.1
End Sub
Private Sub SpinButton2_SpinDown()
'Goes down by 1
Cells(1, 1).Value = Cells(1, 1).Value - 1
End Sub
Private Sub SpinButton1_SpinUp()
'Goes Up by 0.1
Cells(1, 1).Value = Cells(1, 1).Value + 0.1
End Sub
Private Sub SpinButton2_SpinUp()
'Goes up by one
Cells(1, 1).Value = Cells(1, 1).Value + 1
End Sub
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,815
Office Version
  1. 365
Platform
  1. Windows
Is there a need for two spin buttons? Seems excessive clicking a button x number of times when limited to multiples of 1 or 0.1 adjustments.

For a different interface, you could use (e.g.) cells B1 and C1 with values of 0.205 (20.5%) and -0.15 (-15%) and insert following Worksheet_DoubleClick event code into the worksheet object. Then you just double click either B1 or C1 to make change to A1.

The adjustment *is* the value you enter into the cell, which would allow for +ve or -ve whole or mixed numbers, i.e.
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Dim rng As Range
    
    'Two cells that drive the value cell, adjust to suit
    'Can be non-adjacent, e.g. Range("J1,J5")
    Set rng = Range("B1:C1")
    
    If Not Intersect(Target, rng) Is Nothing Then Cells(1, 1).Value = Cells(1, 1).Value + Target.Value
            
    Set rng = Nothing
    Cancel = True
    
End Sub
You can then adjust the formatting of cells B1 and C1 whether it's percent or whole numbers or adjust the code to * 0.01 depending on how you want A1 formatted.

The two specified cells do not need to be adjacent either, so with cell shading and borders, you can format them to visually standout.

Obviously the values can remain as 1 and 0.1. If you want both +ve and -ve for both value changes, you can expand the range to 4 cells or use 3 cells, 2 with the adjustment values and 1 indicating increase or decrease and then adjust the code accordingly.

Saves issues with resizing the spinbox objects or needing to click multiple times, just double-click the required cell.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,132,785
Messages
5,655,290
Members
418,186
Latest member
mrahim

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top