How to edit A Button In Excel
How to Create Macro Buttons in Excel Worksheets
0:00 / 0:00
Quality:
Sharing buttons:
welcome to Excel campus my name is John
and in this video I'm going to explain
how to create a button to run your
macros in Excel so for this tutorial
we're going to look at how to create a
button on the sheet that will run a
macro I do have another tutorial that
explains how to create buttons up in the
ribbon with these custom toolbars I'll
put a link to that in the description
below this video if you're interested in
that as well now this also assumes that
we already have a macro created and if
we jump over here to the Developer tab
open the VBA editor right here is the
macro that we want to run when we click
the button this will update or create
our table of contents and this macro is
stored here in module 1 within this
workbook that I'm working in if you
don't have the macro created yet or you
want to learn how to create this macro I
have other tutorials on that and again
I'll put a link in the description below
this video so let's jump back over to
excel now there are a few different ways
to create buttons in Excel first of all
on the Developer tab we have the insert
drop-down here and we can use the form
control button or the ActiveX control
button however my preferred method is to
use a shape like we have here and to do
that we're going to go to the insert tab
on the ribbon and in this illustrations
group we have the shapes drop-down and
from here we could typically choose
either a rectangle or a rounded
rectangle we can assign macros to any of
the shapes but these ones most resemble
a button so I'll choose the rounded
rectangle and I'll go ahead and draw it
right here on the spreadsheet I will
also explain the difference between
using a shape and a form control like we
have here but for now let's just assign
a macro to this first thing we're going
to do is right-click it and choose edit
text or you can double click it and then
we'll just type the macro name or the
button name in here and it does not have
to be the name of the macro can just be
the name that you want to give to the
button that the user will see we can
also go left click here go up to the
Home tab on the ribbon quickly Center
the text vertical and horizontal align
the text here so it looks nice and then
we'll go ahead and right click the
button and choose assign macro that will
bring up this
sighing macro window and right here
we'll see a list of all the macros that
are currently in this workbook now
sometimes this defaults to all open
workbooks and you might see a lot more
macros but you can filter it down to
just the macros in this workbook and
then we'll go ahead and select the macro
we want to assign which is this one here
TOC list and then click OK and then
we'll just left click off of the shape
somewhere on the sheet and when we hover
the mouse cursor over the shape here
we'll see that we get that left pointer
icon that denotes or tells us that this
is going to run a macro or possibly have
a hyperlink assigned to it but when we
click this now that'll go ahead and run
the macro get a message box here saying
our macro has been run we'll click OK
and that will complete the macro if at
any point you want to change the macro
that the button is assigned to you can
again just right-click the shape choose
a side macro and then choose a different
macro from this list now it's important
to note that you typically want to have
a macro that's stored in this workbook
if you select a macro that's stored in a
different workbook or maybe your
personal macro workbook this will work
on your computer when that file is open
however if you send the file to someone
else they won't necessarily be able to
run the macro because the macro might
not be stored in this workbook so it's
best when you're creating buttons on a
spreadsheet to just choose macros from
this workbook here and that'll make sure
that other users can run these macros
when they open the workbook and again
one of the nice parts about using a
shape is that we can really customize
the style the look and feel of the shape
they also tend to look more like buttons
that we'd find on the modern web so
users will be more familiar with
clicking them so to modify this if you
left click off of the shape here and you
want to select it again you can hold the
control key on the keyboard and then
left-click the shape and that'll select
it instead of running the macro and then
we can go up here to the format tab on
the ribbon and this is where we can
modify the shape style there's a drop
down here you can choose from all of
these pre-built styles here we can also
just choose some of these shape effects
if we want to just give it a shadow
you can change the shape fill color the
outline color text colors and all those
kinds of properties and then one
additional tip is sometimes you'll
notice that if you resize a column that
the shape is over the shape will resize
as well and we can control or prevent
this behavior too so to do that we're
going to right-click on the shape choose
size and properties and then from the
format shape task pane over here in the
property section this might be
collapsing you might need to click it to
expand it we'll see three options here
the default is move in size with cells
and that's what's happening here so if
we resize the cells or insert cells here
insert a column the shape is going to
move again I'm going to hold ctrl + left
click there we can also choose move but
don't size with cells in this case if we
resize a column behind it the shape will
not resize however if we insert columns
here right click insert or ctrl + the
shape will move and then finally left
click oops ctrl left click we can also
choose this option don't move our size
with cells and then the shape will not
move at all so if we were to insert
columns here this shapes not going to
move you can also resize behind here and
the shape will not change at all
now the other options we have here for
macro buttons are form controls and as
you can see the behavior is the same as
a shape it's actually a shape on the
sheet here I'm going to ctrl left click
it just move it down here a bit now
these can be inserted from the Developer
tab the insert drop-down and we have the
form control button right here same
process you can just draw the button on
the sheet that will automatically bring
up the assign macro window you can
select a macro and then hit OK and of
course you can also right-click and here
to edit the text to change the text now
really the performance here is the exact
same or the actions the exact same as
the shape that we assigned the macro to
the only difference is the look and the
feel of the button this is kind of more
of an old-school looking button when you
click it it does kind of depress there
and it still will run the macro so maybe
it looks a little bit more like a button
depending on your preference however
we're very limited on the options we can
change
right click format control to bring up a
list of the formatting options you can
change the font size and style but
that's about it you cannot change the
colors of the button or anything like
that so we're pretty limited on these
form control buttons and that's why I
like to use these shapes instead because
we can use these to match the theme of
our workbook change the colors and do
all that fun stuff now there are also
these active X control buttons and I
almost never use these and don't
recommend using these because the
ActiveX controls are notoriously buggy
in Excel and windows sometimes they can
kind of change size on their own and
especially if you're sending files off
to other users you can't always control
that behavior and sometimes the button
will get really small or really big
depending on what else is going on on
the spreadsheet stuff like that so my
general recommendation is just stay away
from those ActiveX controls especially
for buttons because there's just other
options for us for buttons including the
form control or the shape so I hope
those tips help you create macro buttons
in your spreadsheets again I have other
tutorials that will explain how to
create buttons up here in the ribbon to
run your macros also how to create a
yes/no message box before the macro runs
and plenty of other tutorials on how to
create different types of macros to help
save time with your job and of course if
you have any questions please leave a
comment below if you enjoyed that video
there are a few simple things you can do
to help me out if you are watching this
video on youtube click the like button
below the video and leave a comment with
any questions or feedback and please
don't forget to subscribe to my free
email newsletter to get more tips and
tricks that will help you learn Excel
thanks again for watching and I'll see
you soon
