How can I put a "slider" onto a spreadsheet (as a way of enteringnumbers) ?

[ship]

Hi

Is there any easy way to put enter a number using a "slider" as an
interface? (i.e. Rather than the user entering exact numbers, they
should be able to drag the slider backwards & forwards...).

For example have a look at this site: www.wonga.com

We want to be able to send a protected spreadsheet to our suppliers
with a couple of sliders on it each of which controls the value of a
numeric variable. This way the user can control visually the input...
and, IN REAL TIME, as the user drags the slider they can see how much
money they would make from the deal!

Any thoughts?


Ship
Shiperton Henethe

 

[Mike H]

Try this

View|Toolbars|Forms
Click 'Scroll Bar' and put one on your worksheet

Right click it, format control, and select the control tab
Set the minimum and maximum values and enter a 'Linked cell'
OK

When you protect the sheet you must leave the linked cell unlocked.

Mike

 

[ShaneDevenshire]

Hi,

this feature is built into 2007.

in 2003 you can use a couple of built in toolbars to increase or decrease in
increments that correspond to the choices in the Zoom dropdown - Choose View,
Toolbars, Customize and on the Commands tab, pick the category View and on
the right side find and drag the Zoom in and Zoom out controls to the toolbar.

Unless I missunderstand the previous suggestion won't work unless you add
code. I will supply code in the next email.

 

[ShaneDevenshire]

Hi again,

To add code to the scrollbar (I will use a Control Toolbox scrollbar) -
1. Choose View, Toolbar, Control Toolbox
2. Click the Scrollbar tool and then click once in the spreadsheet.
3. Right click the scrollbar and choose Properties
4. In the Properties box set the LargeChange to 20, the LinkedCell to H1,
Max to 400, Min to 10, and SmallChange to 5, and Value to 100. The close the
Properties sheet.
5. Double-click the Scrollbar which will take you to the VBA area inside a
block of code, modify that code to read:

Private Sub ScrollBar1_Change()
ActiveWindow.Zoom = [h1]
End Sub

6. Close the VBA editor window and click the triangle toolbar button that
has been floating around or the one on the Control Toolbox toolbar (the Exit
Design Mode) button.

 

[ship]

Btw, I am using msExcel 2003 (SP3)


Hi

Thanks Shane - however...
Sorry but the Control Toolbox does not have a button called Scrollbar.
I did try More Controls and there is something called "Microsoft Forms
2.0 ScrollBar" - is that the one that you meant?

Cheers


Ship

Hi again,

To add code to the scrollbar (I will use a Control Toolbox scrollbar) -
1. Choose View, Toolbar, Control Toolbox
2. Click the Scrollbar tool and then click once in the spreadsheet.
3. Right click the scrollbar and choose Properties
4. In the Properties box set the LargeChange to 20, the LinkedCell to H1,
Max to 400, Min to 10, and SmallChange to 5, and Value to 100.  The close the
Properties sheet.
5. Double-click the Scrollbar which will take you to the VBA area inside a
block of code, modify that code to read:

Private Sub ScrollBar1_Change()
    ActiveWindow.Zoom = [h1]
End Sub

6. Close the VBA editor window and click the triangle toolbar button that
has been floating around or the one on the Control Toolbox toolbar (the Exit
Design Mode) button.

--
Thanks,
Shane Devenshire

Hi

Is there any easy way to put enter a number using a "slider" as an
interface? (i.e. Rather than the user entering exact numbers, they
should be able to drag the slider backwards & forwards...).

For example have a look at this site:www.wonga.com

We want to be able to send a protected spreadsheet to our suppliers
with a couple of sliders on it each of which controls the value of a
numeric variable. This way the user can control visually the input...
and, IN REAL TIME, as the user drags the slider they can see how much
money they would make from the deal!

Any thoughts?

Ship
ShipertonHenethe- Hide quoted text -

- Show quoted text -