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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Use two spinners for two cells, and use a formula in a third cell to combine the other two.
 
Upvote 0
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
 
Upvote 0
How do you make it go down?
 
Upvote 0
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.
 
Upvote 0
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??
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

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
Back
Top