How to add activeX buttons programmatically in VBA, filling all rows down a column
<p>My first post here, but have been successfully sourcing solutions and ideas from this website for a while now. So thanks for the collection of solutions and ideas. </p>

<p>Basically, I have a spread sheet application requiring the first column, Column A, to be filled with "Active X" buttons in every cell, looping through for a given quantity. I have posted one such working solution below which makes use of "form type buttons" and a Modules. This exemplifies what I consider my most favored example with working buttons. Once operational the column of buttons will correspond to relative data on the same row, and when clicked will open corresponding folders, and userforms in later developments. </p>

<p>The second post uses the Range function, but obviously doesn't incorporate any buttons to interactive with. However, a mouse click over this Range will obviously activate any code from within the Worksheet_Selection Change procedure...Sorry just stating the obvious!  </p>

<p>What I have been trying to achieve is a version of code employing "activeX" Command Buttons, but after having studied some great tutorials and poured over a range of programing concepts, I still fail miserably to employ OLEObjects. </p>

<p><a href="https://stackoverflow.com/questions/4559094/how-to-add-a-button-programmatically-in-vba-next-to-some-sheet-cell-data">How to add a button programmatically in VBA next to some sheet cell data?</a></p>

<p>Sheet 1 Procedure:
    Sub ColumnA_Buttons()</p>

<pre><code>    Dim buttons As Button
    Dim rng As Range
    Dim LineQty As Variant

        Application.ScreenUpdating = False
        ActiveSheet.buttons.Delete

    LineQty = 5

    For i = 1 To LineQty
        Set rng = ActiveSheet.Range(Cells(i, 1), Cells(i, 1))
        Set buttons = ActiveSheet.buttons.Add(rng.Left, rng.Top, rng.Width, rng.Height)

            With buttons
                .OnAction = "Buttons"
                .Caption = "Line " &amp; i
                .Name = "Line " &amp; i
                End With
        Next i

        Application.ScreenUpdating = True

End Sub

Public Click_Button As Variant ' Make Variable Public for Userform1
</code></pre>

<p>'</p>

<p>Form Button Module:</p>

<pre><code>Sub Line_Buttons()

 Click_Button = Application.Caller

    MsgBox Click_Button &amp; " was Clicked"

        UserForm1.Show 'Launch custom userform

End Sub
</code></pre>

<p>And the next option to be considered is a range detection</p>

<pre><code>Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  ' e.g., range(A1:E1) is clicked
       If Not Application.Intersect(Target, Range("B2:B12")) Is Nothing Then
            MsgBox "You clicked " &amp; Target.Address
       End If
End Sub
</code></pre>