Insert form control button via VBA

Question

I want to insert a button via VBA into my worksheet. Therefore, I tried to go with the simple VBA from here.

Sub Insert_Button()
Sheet1.Shapes("Button 1").Visible = True
End Sub

However, this VBA only works if the button has been inserted and hidden manually before.
I also tried the solution from here but it marked the code red and did not run.

How can I insert a form control button via VBA if the button does no exist in the file yet?

one more: https://stackoverflow.com/questions/30208179/programmatically-add-a-button-with-a-macro-to-an-excel-file — Siddharth Rout, Sep 03 '19 at 08:12
one more: https://stackoverflow.com/questions/46959244/excel-vba-automatically-add-delete-buttons-depending-on-cell-values — Siddharth Rout, Sep 03 '19 at 08:13

score 1 · Answer 1 · answered Sep 03 '19 at 08:07

1

Try this :

ActiveSheet.Shapes.AddFormControl xlButtonControl, 100, 100, 50, 20

Numbers are left position, top position, width and height of inserted button

answered Sep 03 '19 at 08:07

EddiGordo

682
4
10

braX · Accepted Answer · 2019-09-03T08:25:28.493

0

This should work:

ActiveSheet.Buttons.Add(185.25, 61.5, 85.5, 24.75).Select

The numbers are co-ordinates, so if you want to add the button to a cell, you would have to use the cell's left/top... like this

With ActiveSheet
    .Buttons.Add(.Cells(2, 2).Left, .Cells(2, 2).Top, 85.5, 24.75).Select
End With

To include the width and height of a Range like "A1:B2"...

With ActiveSheet
    .Buttons.Add(.Cells(1, 1).Left, .Cells(2, 2).Top, .Cells(1, 1).Width + .Cells(1, 2).Width, .Cells(1, 1).Height + .Cells(2, 2).Height).Select
End With

edited Sep 03 '19 at 08:25

answered Sep 03 '19 at 08:07

braX

11,506
5
20
33

Thanks man for the answer. Is it also possible to set the properties to a range. Let's say the button should appear wihtin cells A1:B2? – Michi Sep 03 '19 at 08:10

Insert form control button via VBA

2 Answers2

Linked