disabling and enabling ActiveX scrollbar using loops

[FrustratedEland]

Im very frustrated as I am sure that this is very simple, but I can't quite pull this one together!

I would like to check against a row of four values that start in A1 - if there is a 1, I would like the corresponding scrollbar (Bars 1 to 4) to be activated, and if there is a 0, to be disabled. I have something in mind along the lines below, and would grateful for any help! This version obviously isnt correct but I cannot find the right format to select the correct bar for the life of me - an earlier attempt didnt have any errors but was reading the bar values which was lovely but not really what I was after! Thank you .

Private Sub EnableScrolls()
Dim i As Long
Dim Sc As ScrollBar

For i = 1 To 4
Sc = ("Bar" & i)
With Sheet1.Range("A1").Cells(1, i)
        If Sheet1.Range("A1").Cells(1, i) = "1" Then
            Sc.Enabled = True
        Else
            Sc.Enabled = False
        End If
End With
Next i
End Sub

 

[Akuini]

@FrustratedEland
Try:

Private Sub EnableScrolls()
Dim i As Long
Dim Sc As Object

For i = 1 To 4
    Set Sc = Sheets("Sheet1").OLEObjects("Scrollbar" & i).Object
    With Sheet1.Range("A1").Cells(1, i)
            If Sheet1.Range("A1").Cells(1, i) = 1 Then
                Sc.Enabled = True
            Else
                Sc.Enabled = False
            End If
    End With
Next i
End Sub

 

[FrustratedEland]

This is absolutely brilliant, thank you. I managed a temporary workaround using shapes.visible, which allowed me to tick through the scrollbar names without throwing errors. However this is what I intended and I would never have got to OLEObjects on my own.

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.