How can I transfer from one cell to another with the spin button?

aminnuto

New Member
Joined
Oct 13, 2004
Messages
15
I want to be able to click on the Spin Button arrow to transfer a value from one cell to another. How would I do that?


I know I can increase/decrease 1 cell with the spin button but I want to transfer the value of 1 cell to another. So, If I have 2 cells that value 100 each, I want to transfer 1 to another so that one cell now has 99 and the other has 101 by clicking the arrow direction on a spin button. How can I do that?
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
I am not sure if there is a better way to achieve your results but this worked for me:

Just create a spin button from ActiveX and open the following code.

VBA Code:
Private Sub SpinButton1_SpinDown()

Dim rng1 As Range
Dim rng2 As Range

Set rng1 = ActiveSheet.Range("A1")
Set rng2 = ActiveSheet.Range("B1")

rng1.Value = rng1.Value + 1
rng2.Value = rng2.Value - 1

End Sub

Private Sub SpinButton1_SpinUp()

Dim rng1 As Range
Dim rng2 As Range

Set rng1 = ActiveSheet.Range("A1")
Set rng2 = ActiveSheet.Range("B1")

rng1.Value = rng1.Value - 1
rng2.Value = rng2.Value + 1

End Sub

Note: change the ranges rng1 and rng2 to follow your needs.
 
Upvote 0
Solution
Great. That almost fixes my issue. It works on whole numbers but not on fractions. How would I do it with decimals?


I want to divide a penny with 4 decimals points.

0.0100
0.0100

I want it to be
0.0098
0.0102


Your formula works great with whole numbers but when I change it to this, nothing happens.



VBA Code:
Private Sub SpinButton1_SpinDown()

Dim rng1 As Range
Dim rng2 As Range

Set rng1 = ActiveSheet.Range("f2")
Set rng2 = ActiveSheet.Range("g2")

rng1.Value = rng1.Value + 0.0002
rng2.Value = rng2.Value - 0.0002

End Sub

Private Sub SpinButton1_SpinUp()

Dim rng1 As Range
Dim rng2 As Range

Set rng1 = ActiveSheet.Range("f2")
Set rng2 = ActiveSheet.Range("g2")

rng1.Value = rng1.Value - 0.0002
rng2.Value = rng2.Value + 0.0002

End Sub
 
Upvote 0
Can you share your workbook? It should also work with fractions.

1672242045369.png


1672242063634.png
 
Upvote 0
Trying but having issues with uploading my workbook. Here is an image. SB1 works with your formula. SB2 doesnt.
 

Attachments

  • Example.png
    Example.png
    124.2 KB · Views: 6
Upvote 0
Since you have two Spin Buttons the second code should be for SpinButton2:

VBA Code:
Private Sub SpinButton2_SpinDown()

Dim rng1 As Range
Dim rng2 As Range

Set rng1 = ActiveSheet.Range("f2")
Set rng2 = ActiveSheet.Range("g2")

rng1.Value = rng1.Value + 0.0002
rng2.Value = rng2.Value - 0.0002

End Sub

Private Sub SpinButton2_SpinUp()

Dim rng1 As Range
Dim rng2 As Range

Set rng1 = ActiveSheet.Range("f2")
Set rng2 = ActiveSheet.Range("g2")

rng1.Value = rng1.Value - 0.0002
rng2.Value = rng2.Value + 0.0002
 
Upvote 0
The 2nd button makes it more complicated. Lets just go with 1 button.


This code works but I need to go down more decimals which does not work.

VBA Code:
Private Sub SpinButton1_SpinDown()

Dim rng1 As Range
Dim rng2 As Range

Set rng1 = ActiveSheet.Range("f2")
Set rng2 = ActiveSheet.Range("g2")

rng1.Value = rng1.Value + 0.02
rng2.Value = rng2.Value - 0.02

End Sub

Private Sub SpinButton1_SpinUp()

Dim rng1 As Range
Dim rng2 As Range

Set rng1 = ActiveSheet.Range("f2")
Set rng2 = ActiveSheet.Range("g2")

rng1.Value = rng1.Value - 0.02
rng2.Value = rng2.Value + 0.02

End Sub


This code does not work.

Code:
Private Sub SpinButton1_SpinDown()

Dim rng1 As Range
Dim rng2 As Range

Set rng1 = ActiveSheet.Range("f2")
Set rng2 = ActiveSheet.Range("g2")

rng1.Value = rng1.Value + 0.002
rng2.Value = rng2.Value - 0.002

End Sub

Private Sub SpinButton1_SpinUp()

Dim rng1 As Range
Dim rng2 As Range

Set rng1 = ActiveSheet.Range("f2")
Set rng2 = ActiveSheet.Range("g2")

rng1.Value = rng1.Value - 0.002
rng2.Value = rng2.Value + 0.002

End Sub
 
Upvote 0
FIXED. Got it. Thanks. The issue was with the $ sign that I had in the field. Everything is working now. Thanks!
 
Upvote 0
The format of the cell that is. It was set to currency. When I changed it to a Number, then the code worked. Thanks again.
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

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