Scroll bar - to increment btwn MIN and MAX value.

[jay]

Scroll bar - to increment btwn MIN and MAX value.

Help,

I don't even know if this is possible;

Issue:
I'm looking to put a "scroll bar" in a cell, which could be used to
increment between a minimum value, and maximum value (values in other
cells). I would like to be able to identify the increment value
(sometimes referred to: step size, or index).

Example: (cells and, value or item)

A1 = 100,000 (minimum value)
A2 = 200,000 (maximum value)
A3 = 5,000 (the 'increment' value, step-size etc.)
A5 = <the scroll bar>
A7 = the resulting value between min and max, as adjusted via the
'scroll bar'

It might be obvious that I want to use the A7 value, as a chart
element.

Thanks for any help you can provide.

jay

 

[Tom Ogilvy]

Sub Macro2()
Dim obj As OLEObject
Dim scrBar As MSForms.ScrollBar
With Range("A5")
.Select
Set obj = ActiveSheet.OLEObjects.Add( _
ClassType:="Forms.ScrollBar.1", _
Link:=False, _
DisplayAsIcon:=False, _
Left:=.Left, Top:=.Top, _
Width:=.Width, Height:=.Height)
End With
Set scrBar = obj.Object
obj.LinkedCell = "'" & ActiveSheet.Name & "'!A7"
scrBar.Min = Range("A1").Value
scrBar.Max = Range("A2").Value
scrBar.SmallChange = Range("a3").Value

End Sub

If you want to be able to change the values in A1, A2 and A3 after the
scrollbar is in place and have those changes reflected in the scroll bar, you
would need to use the worksheet change event to update the properties - they
can't be linked to those cells.

 

[jay]

Hello Tom,

Thank you very much for the code, and I must say I am impressed with
the compactness of it.

However, I get a compiler error of: "User-defined type not defined"
This error, which gets 'blue' colored by the compiler is:

scrBar As MSForms.ScrollBar 'note: Dim is in front of this, just
wanted to show the 'blued' part herein

Am I supposed to put something in cell A5 ?

Am I supposed to add in something under "Tools' -> Reference ?

Thanks for any input you can provide.

jay

 

[Tom Ogilvy]

Well yes. You need a reference to the Microsoft Forms 2.0 Library. If you
manually put a control from the control toolbox toolbar on the worksheet,
you will have such a reference. Otherwise, in the VBE, go to
tools=>References and find the above entry and click the checkbox.