How to add a button programmatically in VBA next to some sheet cell data

Question

I have a function that generates data for say 100 rows  and 2 columns   For each row  in the 3rd column  I need to add a button which  when clicked  brings up a custom modal dialog box giving the user 4 options buttons to choose from   Any idea how to do this

User · Accepted Answer

I think this is enough to get you on a nice path:

Sub a()
  Dim btn As Button
  Application.ScreenUpdating = False
  ActiveSheet.Buttons.Delete
  Dim t As Range
  For i = 2 To 6 Step 2
    Set t = ActiveSheet.Range(Cells(i, 3), Cells(i, 3))
    Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)
    With btn
      .OnAction = "btnS"
      .Caption = "Btn " & i
      .Name = "Btn" & i
    End With
  Next i
  Application.ScreenUpdating = True
End Sub

Sub btnS()
 MsgBox Application.Caller
End Sub

It creates the buttons and binds them to butnS(). In the btnS() sub, you should show your dialog, etc.

Mathematica graphics

User · Answer

Suppose your function enters data in columns A and B and you want to a custom Userform to appear if the user selects a cell in column C  One way to do this is to use the SelectionChange event   Private Sub Worksheet SelectionChange ByVal Target As Range      Dim clickRng As Range     Dim lastRow As Long      lastRow   Range  A1   End xlDown  Row     Set clickRng   Range  C1 C   amp  lastRow    Dynamically set cells that can be clicked based on data in column A      If Not Intersect Target  clickRng  Is Nothing Then         MyUserForm Show   Launch custom userform     End If  End Sub   Note that the userform will appear when a user selects any cell in Column C and you might want to populate each cell in Column C with something like  select cell to launch form  to make it obvious that the user needs to perform an action  having a button naturally suggests that it should be clicked

[excel] How to add a button programmatically in VBA next to some sheet cell data?

Examples related to excel

Examples related to vba

Examples related to button