slider control

[DrJimmy]

Hi,

I'm wanting to use the MS slider control to change values in a formula - is there an easy way of linking a cell in excel to the control to get this?

Cheers

 

[Trevor G]

Hi,

I'm wanting to use the MS slider control to change values in a formula - is there an easy way of linking a cell in excel to the control to get this?

Cheers

Not sure which version of Office you are using. I am using 2007 and you can use a control like a spinner from the Developer tab, you can then select from the Active X controls the spinner then from the properties you can look under the Alphabetic options and you will see LinkedCell then you type in the cell reference.

From there you have to Chnage the design mode option and then as you click the spinners the values will change in the cell.

 

[Rx_]

Re: slider control Excel 2007 changes formula

Will tryt my first Excel 2007, it has to change somewhat from older versions.
Personally, I miss the "buddy control" to a "Linked Cell" that might have let me create a macro-free workbook. Just threw this together and kept it simple.
In older versions, the Property included a "Linked Cell". That seems to be missing in this Active X control.

For Sheet 1, run this macro to set up two values (x and y) and a formula that uses them. Or you can just read the script and manually add this.

    Range("B5").Select
    ActiveCell.FormulaR1C1 = "X value:"   ' a cell describing X
    Range("B7").Select
    ActiveCell.FormulaR1C1 = "Y value:" ' a cell describing Y
    Range("C5").Select
    ActiveCell.FormulaR1C1 = "3"           ' value for X
    Range("C7").Select
    ActiveCell.FormulaR1C1 = "5"           ' Value for Y
    Range("F5").Select
    ActiveCell.FormulaR1C1 = "=SUM(RC[-3]+R[2]C[-3])"  ' Sum X + Y .. your formula
    Range("E5").Select
    ActiveCell.FormulaR1C1 = "Sum:"     ' just a description
    Range("F6").Select
End Sub

Next, under the Developer menu, choose Insert, More Controls - and this example chose the Microsoft Slider Control Version 6.0
To select the slider control (to open the right click menu) and the properties (an also set your max min for the slider) you will need to use the Menu Developer tab - there is a "Design Mode". Just toggle this on to design and off to run or test. Toggle Design Mode On - to open the Properties for the slider, Toggle Design Mode Off to test the slider

In Design Mode - use the same Menu on the ribbon to "View Code".
Since the old Linded Cell no longer exist, open the code window and paste the code into it. This code will run every time the user changes the value on the slider control.

The default name is Slider1. Past this code in:

Private Sub Slider1_Change()
    Range("C5").Select                 ' on the slider change - update the value in C5 with the slider's value    
    ActiveCell.FormulaR1C1 = Me.Slider1.Value
End Sub

Save, close the code window, and take the worksheet out of Design mode.
Move the slider. The X value changes and the Sum formula will also update.
Hint: you can always hide the cells being changed, or cover them up with the slider control itself.