title: How to add activeX buttons programmatically in VBA, filling all rows down a column
body: <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>