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

Hi,
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.  
Book8.xlsx
karinos57Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nicsaintCommented:
You can use the following line of code to set the value of the scroll bar

Sheet1.Shapes("Scroll Bar 1").ControlFormat.Value = 0
0
sir plusSales 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
AND
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(sb.name,len(sb.name)-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
    Next
End With

End Sub

I attach a working sample
Controls.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
karinos57Author Commented:
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.