Fractional Spinner/Scroll Bar

[jschirra]

I would like to use a spinner or scroll bar for numbers between 0.1 and 9.9999. I can do this by setting the Min/Max to 1000 and 99999 and use an intermediate cell that is then divided by 10000. However, the user will not be able to manually enter a number within the range without overwriting the formula in the end cell.

Is there a way to use a spinner or scroll bar with fractional increments where the user can input numbers directly as well?

 

[cerfani]

you can try to use a text change event on a seperate cell where a user can manually enter a value and then and have the event set the spinner's value after you multiply their input by 10000

i dont think i ever used a spinner before but it sounds like their value property is only whole numbers. Sounds like a NumericUpDown from .Net but only for integer type.

 

[jschirra]

you can try to use a text change event on a seperate cell where a user can manually enter a value and then and have the event set the spinner's value after you multiply their input by 10000

i dont think i ever used a spinner before but it sounds like their value property is only whole numbers. Sounds like a NumericUpDown from .Net but only for integer type.

Thanks. I can get it to work using a macro that detects changes in a target cell. Is there a way to do this without VBA?

 

[Eric W]

Here's a possibility:

First, create a list of the values you want. The easiest way to do it is to make a new sheet, let's say it's Sheet18. Put .1 in A1. Now select A1, and from the Home tab, click Fill > Series. Check the Columns radiobutton, put a step value of .0001 and a stop value of 9.9999 and click OK.

Now go to the Developer tab, and instead of a spinner, insert an ActiveX ComboBox. Use the mouse to put it where you want. Right click on it, select Properties. In ListFillRange, put Sheet18!A1:A99000. In LinkedCell put the address of the cell where you want the value. Close the properties box, and exit Design Mode.

Now the user can select the number from the drop-down box, OR they can just select the box and type in the value they want. Or they can select the cell and type it in there!

Hope this helps!

 

[jschirra]

Here's a possibility:

First, create a list of the values you want. The easiest way to do it is to make a new sheet, let's say it's Sheet18. Put .1 in A1. Now select A1, and from the Home tab, click Fill > Series. Check the Columns radiobutton, put a step value of .0001 and a stop value of 9.9999 and click OK.

Now go to the Developer tab, and instead of a spinner, insert an ActiveX ComboBox. Use the mouse to put it where you want. Right click on it, select Properties. In ListFillRange, put Sheet18!A1:A99000. In LinkedCell put the address of the cell where you want the value. Close the properties box, and exit Design Mode.

Now the user can select the number from the drop-down box, OR they can just select the box and type in the value they want. Or they can select the cell and type it in there!

Hope this helps!

Thanks. That will work but scrolling takes forever due to 99000 entries and I can't find a property that can speed up scrolling. With the scroll bar you can specify the LargeChange which helps in getting to one of the entries.

 

[Eric W]

There is a scroll bar on the combobox. Click on the down arrow, grab the slider button with the mouse and drag it up or down until you see the number you want. Sometimes you may have to just get close, then use the down/up buttons to scroll to the exact number. But I've been able to pick any number from that list in just a second or two. I think this would be much faster than a spinner would be.