Spinner

RichP

Board Regular
Joined
Jul 31, 2002
Messages
63
Can someone help please?
I have a question about spinners - is there a way of increasing the maximum value above 30000? I am trying to use a spinner to decrease a value (37000000) by 100 for every click, but I want to be able to decrease the number to zero.
Thanks in advance,
R
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

ChrisUK

Well-known Member
Joined
Sep 3, 2002
Messages
675
I can't be the only one .... what the hell is a spinner !!
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
The Max and Min properties for a SpinButton are Datatype Long, although Integers are recommended.

So set the Max property to 37000000 and the SmallChange property to 100. It worked for me.
 

RichP

Board Regular
Joined
Jul 31, 2002
Messages
63
ChrisUK, on the forms toolbar it is called a spinner, on the control toolbar it is a spin button. I'm using it alter a number without having to re-type every time.

Andrew, worked a treat, thanks. I'd been using the form version, which wouldn't allow a max value of 37,000,000...

R.
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690

ADVERTISEMENT

I'm glad Andrew's post worked for you. Here is another tip for the Spinner (form toolbar). If you should need to increment it by a non integer, say 0.5, you can assign a macro to it

Sub Spinner1_Change()
Range("$A$1").Value = ActiveSheet.Spinners("Spinner 1").Value * 0.5
End Sub
 

RichP

Board Regular
Joined
Jul 31, 2002
Messages
63
Good point about the non-integer values, though personally I prefer the less tidy option of having a cell on the sheet that I can use as a variable (and multiply that by the button value), so that when I change my mind about the 0.5 I don't have to play with the code - I'm not that confident with VB :).
Thanks for the tip,
R.
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690

ADVERTISEMENT

On 2002-10-11 10:08, RichP wrote:
Good point about the non-integer values, though personally I prefer the less tidy option of having a cell on the sheet that I can use as a variable (and multiply that by the button value), so that when I change my mind about the 0.5 I don't have to play with the code - I'm not that confident with VB :).
Thanks for the tip,
R.

When I need the flexibility to cahnge the increment value, I use a named range in my code, i.e.

Sub Spinner1_Change()
Range("$A$1").Value = ActiveSheet.Spinners("Spinner 1").Value * Range("SpinnerStep")
End Sub
 

RichP

Board Regular
Joined
Jul 31, 2002
Messages
63
Do you know if there is a way of doing this without macros? The sheet I'm putting together will eventually be used by people not too familiar with excel (hence spinners to reduce the amount of typing) and I have a feeling they're going to be put off by the enable macros sheet, and I'm not going to be able to turn down the security setting on their computer....Any thoughts?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
If you add a SpinButton to your worksheet and set the LinkedCell property, you can do whatever you like by referencing the value in that cell. There will be no code so the user won't be prompted to enable macros.
 

Forum statistics

Threads
1,143,613
Messages
5,719,710
Members
422,242
Latest member
hishamkhatri

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
Top