Selecting named activex scrollbars and then setting scrollbar.value

[FrustratedEland]

Back again with these pesky scrollbars.

So, I am concatenating the scrollbar name to get the right scrollbar, and then trying to set the scrollbar to a value. Once more, I am running into errors. This is a simplified version of what I am trying to do.
The scrollbar is called SCBlue in this example, and A1 contains 'Blue'. B1 contains the position that I would like to set the scrollbar to.

Private Sub test()
Dim Bar As Object
Dim Position As Long
Category = Sheet1.Range("A1")
Barname = ("SC" & Category)
Position = Sheet1.Range("B1").Value
Set Bar = Sheets("Sheet1").OLEObjects("Barname").Object
With Bar.Value = Position
End With
End Sub

As a check, everything works fine 'manually', if I change lines 7 to 9 to
SCBlue.Value = Position

Grateful as always for any help!

 

[Jaafar Tribak]

Try to always declare your variables with the correct data types :

Option Explicit

Private Sub test()
    Dim Bar As Object
    Dim Category As String, Barname As String
    Dim Position As Long
    
    Category = Sheet2.Range("A1")
    Barname = ("SC" & Category)
    Position = Sheet1.Range("B1")
    Set Bar = Sheet1.OLEObjects(Barname).Object
    Bar.Value = Position
End Sub

 

[FrustratedEland]

Try to always declare your variables with the correct data types :

Option Explicit

Private Sub test()
    Dim Bar As Object
    Dim Category As String, Barname As String
    Dim Position As Long
   
    Category = Sheet2.Range("A1")
    Barname = ("SC" & Category)
    Position = Sheet1.Range("B1")
    Set Bar = Sheet1.OLEObjects(Barname).Object
    Bar.Value = Position
End Sub

Woah!

Although, I do note that if I take out my quotations around Barname in L7 it does work.

I am so used to getting Objects wrong I cant see the wood for the trees it would seem.

Thank you wholeheartedly for taking the time to help me!