VBA Add button, set the caption and link it to a method

Question

I want to add a button on a new worksheet, set the caption and link it to a method in a module. Any help would be appreciated. Thanks!

what type of button, Forms or ActiveX?

brettdj
– brettdj

2012-01-21 01:17:21 +00:00
Commented Jan 21, 2012 at 1:17 — brettdj
– brettdj, Commented Jan 21, 2012 at 1:17

brettdj · Accepted Answer · 2012-01-21 02:02:50Z

9

Dim b As Excel.Shape
Set b = ActiveSheet.Shapes.AddFormControl(xlButtonControl, 10, 10, 100, 100)
b.OnAction = "AMacro"
b.OLEFormat.Object.Text = "Run a macro"

edited Jan 21, 2012 at 2:02

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

answered Jan 21, 2012 at 0:41

GSerg

78.8k18 gold badges173 silver badges377 bronze badges

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

4 Comments

alyon2002 Over a year ago

This works to insert a button but when I click on the new button it doesn't run the macro, it selects the button and highlights the caption for editing, also it runs the macro when its created I only need it ran when it is clicked? What am I doing wrong

brettdj Over a year ago

@alyon2002 are you left clicking the button to run it, or right clicking which will bring up design mode?

brettdj Over a year ago

Gserg, I realised at some stage I accidentally downvoted this by mistake as I scrolled the page. I have edited your post code formatting to remedy this. My apologies

alyon2002 Over a year ago

I was right clicking, turns out I needed to left click and exit text edit mode, this worked great thank you

T I · Accepted Answer · 2012-01-21 11:01:00Z

2

~~Try this taken from http://www.mrexcel.com/forum/showthread.php?t=43637~~

Sub CreateButton()
    ActiveSheet.Buttons.Add(199.5, 20, 81, 36).Select
    Selection.Name = "New Button"
    Selection.OnAction = "CheckTotals"
    ActiveSheet.Shapes("New Button").Select
    Selection.Characters.Text = "Check Totals"
End Sub

Edit: GSerg's answer is admittedly better then this as this was just a quick copy and paste job for illustrative purposes. I will leave the answer up as a comparative against a better way to programme VBA and a small reference as to why you should avoid Selection. I'd imagine the code snippet would be something produced via the macro recorder and clearly this will never be optimal.

edited Jan 21, 2012 at 11:01

answered Jan 21, 2012 at 0:35

T I

9,9634 gold badges31 silver badges51 bronze badges

4 Comments

GSerg Over a year ago

Selecting things in Excel for acting on them is evil.

brettdj Over a year ago

@Gserg Its certainly not optimal, but as its working code it didn't deserve the downvote (from whoever did that)

brettdj Over a year ago

@alyon2002 Primarily as it is unncessary but it slows code down. It can also cause errors, ie the object may not be directly selectable (the worksheet may be hidden), it may trigger other Event driven code etc.

alyon2002 Over a year ago

@Tom, I tried your example it it worked after I put Character.Text before ("New Button").select, thanks for the help!

Collectives™ on Stack Overflow

VBA Add button, set the caption and link it to a method

2 Answers 2

4 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related