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?

Checking: You want something like a List/ComboBox in the third column, but modal? — Dr. belisarius, Dec 30 '10 at 01:12

Dr. belisarius · Accepted Answer · 2015-07-23T18:06:04.293

114

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

edited Jul 23 '15 at 18:06

answered Dec 30 '10 at 02:55

Dr. belisarius

60,527
15
115
190

this works if you change 'target to 't' and 'tLeft' to 't.Left' – datatoo Dec 30 '10 at 20:33
1

Great answer. After reading it, I sort of "discovered" this for myself by recording a macro. But I can't seem to find any reference to Buttons, either in the Help, or the Object browser. I must not be looking in the right place? – DaveU Oct 05 '16 at 03:24
Thank you for the code! However, when I try to run it I get the error message "Expected end of statement" on the `Dim btn as button` line. I'm using Notepad++ to create my .vbs file, but it won't compile – cdomination Oct 21 '16 at 16:20
Is there a way to pass params to btnS ? lets say cell B2 had a value that we wanted to pass to the btnS sub – Wilco Apr 02 '21 at 18:30

score 4 · Answer 2 · edited Jun 07 '22 at 18:47

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" & 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)

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

2 Answers2

Linked