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?
 
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
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
 
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
0
 
karinos57Author Commented:
thanks
0
All Courses

From novice to tech pro — start learning today.