Add 2 spinners for a single cell?

[venshi]

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!!!!

 

[shg]

Use two spinners for two cells, and use a formula in a third cell to combine the other two.

 

[My Aswer Is This]

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

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]

How do you make it go down?

 

[My Aswer Is This]

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]

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]

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

 

[venshi]

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]

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

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]

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.

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.