change the current value to zero (ScrollBar "Control Toolbox" properties )

i have created ScrollBar in excel so what i want to do is that to change the current value to Zero using vba when user clicks the Run button.  See attached for more details.  thanks.  
Who is Participating?
sir plusConnect With a Mentor Sales ManagementCommented:
Put sheet in designermode
Dbl click on your button

Replace the macro that opens with

Private Sub command_Click()
Me.Shapes("Scroll Bar 1").ControlFormat.Value = 0
End Sub

You have user an activex button and a forms scrollbar

I have added an activex button and scrollbar to the attached sheet
you can refer to activex objects direct without shapes.

Code becomes simpler
Private Sub CommandButton1_Click()
Me.ScrollBar1.Value = 0
End Sub

and far more formattiing options, as well I can choose ScrollBar1 fro a popup list in vba as soon as I type the "." after Me

Only drawback is you can't refer by its name so you cant use
for n = 1 to 10
Me.ScrollBar&N.Value = 0
next n
to reset the first 10 scrollbars
Activex objects are indexed in order created
so to find first 10 you'd have to use
for each sb in me.scrollbars
if right(,len("ScrollBar") )

Private Sub CommandButton1_Click()
Me.ScrollBar1.Value = 0
End Sub

The following should help you get the difference and how to control each

Private Sub CommandButton1_Click()

Dim sb As Object
Dim oAX As Object

Const oSBFNameStyle = "Scroll Bar "
For Each sb In Me.ScrollBars
If CInt(Right(sb.Name, Len(sb.Name) - Len(oSBFNameStyle))) <= 10 Then
sb.Value = 10
End If
Next sb

Const oAXNameStyle = "ScrollBar"
With Me
    For Each oAX In .OLEObjects
        If TypeName(oAX.Object) = "ScrollBar" Then 'Can use .progID = "Forms.ScrollBar.1"
            If CInt(Right(oAX.Name, Len(oAX.Name) - Len(oAXNameStyle))) <= 10 Then
             oAX.Object.Max = 100
             oAX.Object.Value = 10
            End If
        End If
End With

End Sub

I attach a working sample
nicsaintConnect With a Mentor Commented:
You can use the following line of code to set the value of the scroll bar

Sheet1.Shapes("Scroll Bar 1").ControlFormat.Value = 0
karinos57Author Commented:
All Courses

From novice to tech pro — start learning today.