Spinbutton
 

I have a worksheet that is used for calculating asset allocations. I would
like to add a spin button where the user can enter a value and spin it up and
down. Essentially I want the first button to spin and it's value to be
inversely related to a seperate value (with it's own spinner) with both of
them added together equaling 100%. So I would click up on Cell A1's Spinner
with a value of 20. This would change A1's value to 21 and would inversely
change Cell A4's value from 80 to 79 equaling 100. That and I want it to
bring up an orginal value or reset back to an orginal value. If any one
could help show me or write a sample code that I can take and run with that
would be GREAT.

Spinbutton
 

Try this.

Create SpinButton1 from the Control Toobox and set the Linked Cell
property to A1
and it's code as follows:

Private Sub SpinButton1_Change()
Range("A2") = 100 - Range("A1")
End Sub

Create SpinButton2 from the Control Toobox and set the Linked Cell
property to A2
and it's code as follows:

Private Sub SpinButton2_Change()
Range("A1") = 100 - Range("A2")
End Sub

Spinbutton
 

Hi Ben,

Since you have a relatively simple requirement for your spinner button, you
can use the spinner control from the "Forms" toolbar (rather than the
"Control Toolbox" which provides you with a somewhat heavier-duty spinner
control). And the nice part of this type of control is that you won't need
any VBA code to accomplish your goal (at least with respect to the spinner
control). In Excel, click on "View|Toolbars|Forms" and select the spinner
control from the Forms dialogue. Place and size the spinner beside the
appropriate cell (A1 in your example below) and then right-click on it and
select "Format control..." from the right-click menu. In the Format Control
dialogue, reset the minimum value if it is not going to be zero (it probably
is zero based on your example) and, for your example, reset the maximum to
100. Set the "Cell link" equal to the cell that will hold the result of
spinning the button up or down (in this case, A1). If you'd like the spinner
to have a 3-d look, check the 3-d check box. Then click OK. Now simply set
cell A4 from your example equal to "=100-A1". If you'd like to have a reset
switch to change the current value in cell A1 (and any other data cells) to
some pre-defined value, you could use the command button from that same Forms
Menu. Now, here, you'd need to attach a small macro; something like:

Sub cmdReset()
Range("A1").Value = 0
End Sub

To attach this macro to the command button, just choose the button control
from the Forms toolbar, place it on your spreadsheet, and the Macro dialogue
will automatically appear. Give the macro a name ("cmdReset" in this case),
and click the "New" button. This opens the VBA editor where you'll insert
the above code and then return to Excel. You can change the button's text by
right-clicking on the button and selecting "Edit text" from the right-click
menu (enter something like RESET. The code shown above will simply cause
cell A1 to get reset to zero. But you could place a set of default values
somewhere else on your spreadsheet and use them as your reset default. Let's
say you put the reset value for A1 in cell G1. Just change the above code
line to: Range("A1").Value = Range("G1").Value. The advantage here is that
the user can set their own defaults if desired (or you can set the defaults
right on the front-end without having to go over to the VBA editor. Hope
this helps.

Paul

"Ben B" wrote:

I have a worksheet that is used for calculating asset allocations. I would
like to add a spin button where the user can enter a value and spin it up and
down. Essentially I want the first button to spin and it's value to be
inversely related to a seperate value (with it's own spinner) with both of
them added together equaling 100%. So I would click up on Cell A1's Spinner
with a value of 20. This would change A1's value to 21 and would inversely
change Cell A4's value from 80 to 79 equaling 100. That and I want it to
bring up an orginal value or reset back to an orginal value. If any one
could help show me or write a sample code that I can take and run with that
would be GREAT.

Spinbutton
 

Paul

I have set up a spinner but when I click on it with the right mouse button
and go to the Format Control Option the Tab for Control is missing. Please
can you advise me???
--
Regards Clair


"Paul Mathews" wrote:

Hi Ben,

Since you have a relatively simple requirement for your spinner button, you
can use the spinner control from the "Forms" toolbar (rather than the
"Control Toolbox" which provides you with a somewhat heavier-duty spinner
control). And the nice part of this type of control is that you won't need
any VBA code to accomplish your goal (at least with respect to the spinner
control). In Excel, click on "View|Toolbars|Forms" and select the spinner
control from the Forms dialogue. Place and size the spinner beside the
appropriate cell (A1 in your example below) and then right-click on it and
select "Format control..." from the right-click menu. In the Format Control
dialogue, reset the minimum value if it is not going to be zero (it probably
is zero based on your example) and, for your example, reset the maximum to
100. Set the "Cell link" equal to the cell that will hold the result of
spinning the button up or down (in this case, A1). If you'd like the spinner
to have a 3-d look, check the 3-d check box. Then click OK. Now simply set
cell A4 from your example equal to "=100-A1". If you'd like to have a reset
switch to change the current value in cell A1 (and any other data cells) to
some pre-defined value, you could use the command button from that same Forms
Menu. Now, here, you'd need to attach a small macro; something like:

Sub cmdReset()
Range("A1").Value = 0
End Sub

To attach this macro to the command button, just choose the button control
from the Forms toolbar, place it on your spreadsheet, and the Macro dialogue
will automatically appear. Give the macro a name ("cmdReset" in this case),
and click the "New" button. This opens the VBA editor where you'll insert
the above code and then return to Excel. You can change the button's text by
right-clicking on the button and selecting "Edit text" from the right-click
menu (enter something like RESET. The code shown above will simply cause
cell A1 to get reset to zero. But you could place a set of default values
somewhere else on your spreadsheet and use them as your reset default. Let's
say you put the reset value for A1 in cell G1. Just change the above code
line to: Range("A1").Value = Range("G1").Value. The advantage here is that
the user can set their own defaults if desired (or you can set the defaults
right on the front-end without having to go over to the VBA editor. Hope
this helps.

Paul

"Ben B" wrote:

I have a worksheet that is used for calculating asset allocations. I would
like to add a spin button where the user can enter a value and spin it up and
down. Essentially I want the first button to spin and it's value to be
inversely related to a seperate value (with it's own spinner) with both of
them added together equaling 100%. So I would click up on Cell A1's Spinner
with a value of 20. This would change A1's value to 21 and would inversely
change Cell A4's value from 80 to 79 equaling 100. That and I want it to
bring up an orginal value or reset back to an orginal value. If any one
could help show me or write a sample code that I can take and run with that
would be GREAT.