software

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.

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(quot;A2quot;) = 100 - Range(quot;A1quot;)
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(quot;A1quot;) = 100 - Range(quot;A2quot;)
End SubHi Ben,

Since you have a relatively simple requirement for your spinner button, you
can use the spinner control from the quot;Formsquot; toolbar (rather than the
quot;Control Toolboxquot; 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 quot;View|Toolbars|Formsquot; 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 quot;Format control...quot; 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 quot;Cell linkquot; 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 quot;=100-A1quot;. 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(quot;A1quot;).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 (quot;cmdResetquot; in this case),
and click the quot;Newquot; 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 quot;Edit textquot; 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(quot;A1quot;).Value = Range(quot;G1quot;).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

quot;Ben Bquot; wrote:

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

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 Clairquot;Paul Mathewsquot; wrote:

gt; Hi Ben,
gt;
gt; Since you have a relatively simple requirement for your spinner button, you
gt; can use the spinner control from the quot;Formsquot; toolbar (rather than the
gt; quot;Control Toolboxquot; which provides you with a somewhat heavier-duty spinner
gt; control). And the nice part of this type of control is that you won't need
gt; any VBA code to accomplish your goal (at least with respect to the spinner
gt; control). In Excel, click on quot;View|Toolbars|Formsquot; and select the spinner
gt; control from the Forms dialogue. Place and size the spinner beside the
gt; appropriate cell (A1 in your example below) and then right-click on it and
gt; select quot;Format control...quot; from the right-click menu. In the Format Control
gt; dialogue, reset the minimum value if it is not going to be zero (it probably
gt; is zero based on your example) and, for your example, reset the maximum to
gt; 100. Set the quot;Cell linkquot; equal to the cell that will hold the result of
gt; spinning the button up or down (in this case, A1). If you'd like the spinner
gt; to have a 3-d look, check the 3-d check box. Then click OK. Now simply set
gt; cell A4 from your example equal to quot;=100-A1quot;. If you'd like to have a reset
gt; switch to change the current value in cell A1 (and any other data cells) to
gt; some pre-defined value, you could use the command button from that same Forms
gt; Menu. Now, here, you'd need to attach a small macro; something like:
gt;
gt; Sub cmdReset()
gt; Range(quot;A1quot;).Value = 0
gt; End Sub
gt;
gt; To attach this macro to the command button, just choose the button control
gt; from the Forms toolbar, place it on your spreadsheet, and the Macro dialogue
gt; will automatically appear. Give the macro a name (quot;cmdResetquot; in this case),
gt; and click the quot;Newquot; button. This opens the VBA editor where you'll insert
gt; the above code and then return to Excel. You can change the button's text by
gt; right-clicking on the button and selecting quot;Edit textquot; from the right-click
gt; menu (enter something like RESET. The code shown above will simply cause
gt; cell A1 to get reset to zero. But you could place a set of default values
gt; somewhere else on your spreadsheet and use them as your reset default. Let's
gt; say you put the reset value for A1 in cell G1. Just change the above code
gt; line to: Range(quot;A1quot;).Value = Range(quot;G1quot;).Value. The advantage here is that
gt; the user can set their own defaults if desired (or you can set the defaults
gt; right on the front-end without having to go over to the VBA editor. Hope
gt; this helps.
gt;
gt; Paul
gt;
gt; quot;Ben Bquot; wrote:
gt;
gt; gt; I have a worksheet that is used for calculating asset allocations. I would
gt; gt; like to add a spin button where the user can enter a value and spin it up and
gt; gt; down. Essentially I want the first button to spin and it's value to be
gt; gt; inversely related to a seperate value (with it's own spinner) with both of
gt; gt; them added together equaling 100%. So I would click up on Cell A1's Spinner
gt; gt; with a value of 20. This would change A1's value to 21 and would inversely
gt; gt; change Cell A4's value from 80 to 79 equaling 100. That and I want it to
gt; gt; bring up an orginal value or reset back to an orginal value. If any one
gt; gt; could help show me or write a sample code that I can take and run with that
gt; gt; would be GREAT.