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
– Dr. belisarius, Commented Dec 30, 2010 at 1:12

Dr. belisarius · Accepted Answer · 2015-07-23 18:06:04Z

120

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, 2015 at 18:06

answered Dec 30, 2010 at 2:55

Dr. belisarius

61.2k15 gold badges121 silver badges198 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

datatoo Over a year ago

this works if you change 'target to 't' and 'tLeft' to 't.Left'

DaveU Over a year ago

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?

cdomination Over a year ago

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

Wilco Over a year ago

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

Chris Peh Dec 16, 2024 at 0:20

Where do I need to put the Sub btnS? It gives me an error on pressing: Can't run the macro

|

Poyda · Accepted Answer · 2022-06-07 18:47:10Z

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)

Collectives™ on Stack Overflow

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

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related