Assigning captions to dynamic form controls buttons based on cell value

Question

I am pretty new to Excel VBA so I apologize in advance if something will be vaguely described. I am having a code, which inserts on the "Demand Overview" worksheet into column W certain number of product names ("Product X", "Product Y" etc....the number of products are dynamically changing from 1 to 100), always starting in the cell W2 and always skipping one cell between each product (like Product X would be in W2, Product Y in W4 etc.) What I would like to code in VBA, is to create a form control button for each of the product names and to change their caption to the name of the products in column W. I could write the code for dynamically creating as many form control buttons as many product names are in column W, however, I am struggling to assign the captions to them, since I always get the error: "Unable to get the Buttons property of the Worksheet class"

The assigning logic would be that Button 1 would get the product name located in W2, Button 2 would get the product name located in W4 etc.

Any help would be highly appreciated! Thanks in advance!

Sub AddInfo()


Dim butn As Button
Dim rng As Range
Dim trial As Range
Dim i As Integer

Sheets("Demand Overview").Select
Set rng = ActiveSheet.Range("S:S")
For Each trial In ActiveSheet.Range("W:W")

 If trial.Value <> "" Then Set butn = ActiveSheet.Buttons.Add(rng.Left, 
 trial.Top, 150, 26)

Next trial

'the code works until this point

With ActiveSheet
For i = 1 To 100
.Buttons("Button" & i).Caption = Range("W" & 2 * i)
 Next i
 End With

 End Sub

Answer 1

Try this. Think it makes more sense to reference the button as you create it so you know you are referencing the correct one.

Sub AddInfo()

Dim butn As Button
Dim rng As Range
Dim trial As Range
Dim i As Long

With Sheets("Demand Overview")
    Set rng = .Range("S:S")
    For Each trial In .Range("W:W")
        If trial.Value <> "" Then
            Set butn = .Buttons.Add(rng.Left, trial.Top, 150, 26)
            i = i + 1
            butn.Caption = .Range("W" & 2 * i).Value
        End If
    Next trial
End With

End Sub

By the way, I wouldn't loop through the whole of W, find the last row and limit your loop to what is necessary rather than all million rows.