Spinner

[RichP]

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

 

[ChrisUK]

I can't be the only one .... what the hell is a spinner !!

 

[Andrew Poulsom]

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]

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]

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]

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]

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]

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]

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.