Repeatedly adding a macro enabled button through VBA

Question

Trying to create an inputbox that a user can type notes into, those notes will be input onto the same document and simultaneously create a macro enables “thumbs up icon” that will hold a macro that other members can “thumbs up” ideas they want to promote. The code below is causing the button to duplicate itself on the same cell. I need it to instead apply to the next available cell.

Sub VBA_Input_Idea_inputbox() 
 Dim MyInp As String
 Dim NextRow As Long

 MyInp = VBA.Interaction.InputBox("Please input idea", "LEARNING 
    REQUEST")
  If MyInp = "" Then Exit Sub
 NextRow = Cells(Rows.Count, 3).End(xlUp).Row + 1
 Range("C" & NextRow).Value = 
 Excel.WorksheetFunction.Proper(MyInp)
 Range("A" & NextRow).Select
 ActiveSheet.Buttons.Add(0.75, 145.5, 42, 24.75).Select
 Selection.OnAction = "Addcount"
  End Sub

Try to [avoid using activate and select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code. It will make it easier to get what you want — cybernetic.nomad, Feb 27 '19 at 16:01
It's something to do with the adding then, did you do any investigation on `buttons.add` ? As you are using `ActiveSheet.Buttons.Add(0.75, 145.5, 42, 24.75).Select` to add, i.e. the same place each time :o). You need to change these numbers, so use `cells(1,2).top` etc to sort it all out. — Nathan_Sav, Feb 27 '19 at 16:09
https://stackoverflow.com/questions/4559094/how-to-add-a-button-programmatically-in-vba-next-to-some-sheet-cell-data — Nathan_Sav, Feb 27 '19 at 16:14
Possible duplicate of [How to add a button programmatically in VBA next to some sheet cell data?](https://stackoverflow.com/questions/4559094/how-to-add-a-button-programmatically-in-vba-next-to-some-sheet-cell-data) — Nathan_Sav, Feb 27 '19 at 16:15
Nathan- your suggestion for "How to add a button programmatically in VBA next to some sheet cell data?" spot on. Thank you for linking. — JMSC, Feb 27 '19 at 17:03

score 0 · Answer 1 · answered Feb 27 '19 at 16:58

Something like this:

Sub VBA_Input_Idea_inputbox()
    Dim MyInp As String
    Dim NextRow As Range, btn

    MyInp = VBA.Interaction.InputBox("Please input idea", "LEARNING REQUEST ")
    If MyInp = "" Then Exit Sub
    With ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).EntireRow
       .Cells(3).Value = Application.Proper(MyInp)
       Set btn = ActiveSheet.Buttons.Add(.Cells(1).Left, .Cells(1).Top, _
                                         .Cells(1).Width, .Cells(1).Height)
       btn.OnAction = "Addcount"
    End With
End Sub

Repeatedly adding a macro enabled button through VBA

1 Answers1