Focus video player for keyboard shortcuts
Auto
- 720p
- 540p
- 360p
1.00x
cc
- 0.50x
- 0.75x
- 1.00x
- 1.25x
- 1.50x
- 1.75x
- 2.00x
We hope you enjoyed this lesson.
Cool lesson, huh? Share it with your friends
About this lesson
How to run your macro from a worksheet button.
Exercise files
Download this lesson’s related exercise files.
Running Macros: Using buttons23.4 KB Running Macros: Using buttons - Completed
25.9 KB
Quick reference
Running Macros: Using Buttons
Adding a button to a worksheet in order to launch a macro.
When to use
To allow a user to run a macro by the click of a button right on the worksheet.
Instructions
Inserting a Button
- Go to the Developer tab --> Insert --> Button icon (blank rectangle).
- In worksheet, left click and drag to draw a rectangle.
- When you let go of the left click, the Assign Macro dialog opens
- Select the macro to use and click OK.
Editing Buttons
- Right click the button and select Edit Text.
- Simple formatting options are also available in Format Control.
Hints & tips
- Change the label on your button to something more descriptive
- This will help engage the user to click it
- 00:05 So in this video, I'm gonna give you a third way to kick off macros because again
- 00:09 visiting back on these things, sending somebody into the macros dialog is not
- 00:13 good because its going to freak them out.
- 00:15 They might roll or run the wrong macros or something like that.
- 00:17 It's not always the best.
- 00:18 Using a keyboard shortcut is fantastic, providing that piece of
- 00:22 intellectual capital gets rolled over between new people coming in.
- 00:27 The unfortunate part about this is that if they don't know to look at this or
- 00:30 if they only use this once a year or something like that,
- 00:33 it's very easy to forget about this.
- 00:35 And that way we're gonna lose some of the intellectual capital that's gone into
- 00:39 building up the solution.
- 00:40 So people are gonna start doing things manually again when they could do
- 00:42 them faster.
- 00:43 It may not be consistent any more which is part of the point of using the macro to
- 00:46 begin with.
- 00:47 So keyboard shortcuts are cool, but if they're not really documented well, and
- 00:51 the file's not in use all the time, they're a challenge.
- 00:53 So wouldn't it be nice if we could add some kind of a piece of chrome on
- 00:58 the canvas for the worksheet that somebody could just click on it and it would work.
- 01:02 That would be pretty cool.
- 01:03 Well, as it happens, there is something that allows us to do this, and
- 01:07 it's called a button.
- 01:08 As a matter of fact, that's the only purpose for
- 01:11 a button in an Excel spreadsheet.
- 01:12 There's nothing else it does.
- 01:14 The only thing that it actually does for us is run a macro.
- 01:19 So the question is, where do you find it?
- 01:21 You might be tempted to jump over to the Insert tab and
- 01:24 use something like a text box or something like that.
- 01:26 But that's actually not what you do.
- 01:27 And this is probably why you may not have actually found these before.
- 01:31 On the Developer tab,
- 01:32 there is a section over here on the Controls area called Insert.
- 01:38 And on the Insert button, you'll find that there are a couple of different things.
- 01:41 There is a Form Control and there are ActiveX Controls.
- 01:45 Now, ActiveX Controls are very pretty.
- 01:48 There's all kinds of neat things they can do.
- 01:49 They're much more robust than Form Controls.
- 01:51 The challenge is that you need to understand VBA
- 01:54 to a fairly good degree before you start working with these.
- 01:56 So I'm gonna tell you, ignore the ActiveX controls for right now.
- 01:59 We're gonna focus on the form controls.
- 02:00 The form controls can be used without VBA at all with the exception of the button.
- 02:06 The button, the only job, as I say, is to launch a VBA macro.
- 02:10 These other ones, if you've never actually worked with form controls,
- 02:12 there's a combo box here that you can actually use.
- 02:15 It has a little drop down list and check boxes and radio buttons.
- 02:17 So I encourage you to look those up.
- 02:19 They're kind of more of a regular Excel thing.
- 02:21 But for right now, what I'm gonna do is I'm gonna grab this button form control,
- 02:24 and I'm going to click on it.
- 02:26 And then when I come back to the worksheet, nothing really happens, so
- 02:29 what I am going to do is left-click, and drag.
- 02:31 And it's going to make a nice little square or rectangle for me,
- 02:35 and as soon as I let it go, it pops up and
- 02:37 it gives me the only option that it will allow me to use.
- 02:41 Pick your macro.
- 02:42 So we'll say well, okay, gonna use roll forward.
- 02:45 So it says okay, no problem, and it tells me this macro rolls, hey, look at that.
- 02:48 Here's the description, rolls the file forward.
- 02:49 I can get at it by pressing Ctrl+Shift+R.
- 02:51 Interesting, I wonder if we'll be able to use Ctrl+Shift+R,
- 02:55 use the keyboard shortcut as well as using the button.
- 02:57 Let's try this out.
- 02:58 We'll say OK.
- 02:59 Now when it lands on the grid, you'll notice it's got these dots around it.
- 03:04 These are selection handles.
- 03:05 This means that this is currently in Edit mode, Design mode.
- 03:09 So what we're gonna do is we're gonna go and
- 03:11 we're gonna highlight the text on this.
- 03:13 And I find this is really, really awkward.
- 03:15 You can't left-click and drag to get it.
- 03:17 So generally what I will do is I will click in front of it and
- 03:19 I'll press delete a few times.
- 03:21 And if I'm really feeling lucky and things are working well,
- 03:23 it actually updates on screen.
- 03:24 Otherwise it may not.
- 03:25 But the key part here is I'm gonna go and type in something like Roll Forward.
- 03:30 Now you'll notice here I can use spaces.
- 03:33 In my macro I can't, but here I can.
- 03:37 So that's kinda cool.
- 03:38 I can put anything I want in here.
- 03:39 Now what I'm gonna do is I'm gonna go and commit this.
- 03:43 But I'm not gonna press Enter cuz that would put a hard return on the button.
- 03:46 Instead what I'm gonna do is I'm gonna click somewhere outside in
- 03:48 the worksheet grid.
- 03:50 Once I do that, it deselects the button.
- 03:53 And now what you'll find is when I mouse over it, I get this little hand.
- 03:56 Now what if I realize that I made a spelling mistake.
- 03:58 In order to get back into Edit mode, we right-click the button.
- 04:02 There we go, and then we can say Edit Text, all right?
- 04:05 So we can do a little bit of formatting to it as well, a tiny bit but
- 04:08 not a whole a heck of a lot.
- 04:10 So it just gets us into fonts and stuff like that, but not too much.
- 04:13 You can't change really colors or anything like that, I don't believe, nope.
- 04:16 So never mind.
- 04:17 We'll say cancel on that.
- 04:19 We'll leave this as a plain, boring gray.
- 04:21 This is one of the reasons people are attracted to ActiveX buttons,
- 04:23 you can change the colors there.
- 04:24 But regular button works better for us here.
- 04:27 So we're gonna click OK.
- 04:28 Now let's try it.
- 04:30 Look at that, it works nicely, right?
- 04:33 So let's go in and put in our 4,000 here and
- 04:36 we'll put 1,500 on this one here, oops, 1,500, here we go.
- 04:40 We'll drop this one down a little bit.
- 04:41 Can I still use Ctrl+Shift+R?
- 04:45 Yes, I can.
- 04:45 That's pretty cool.
- 04:46 So if I go and say let's lock in another 3,000 on this,
- 04:50 another 500 over this guy here, Roll Forward, works beautifully.
- 04:55 Okay, this little button, I love it because you can have multiple buttons,
- 04:59 one for each macro that you wanna run.
- 05:01 And the nice piece about this is that even if I don't open up this file for
- 05:04 a year, it's still pretty obvious what that's supposed to do.
- 05:07 So this isn't gonna freak users out, they don't have to hunt for things or whatnot.
- 05:11 You don't have to have your keyboard shortcuts set up.
- 05:13 You can if you want to, but it's great because it actually gives us some nice
- 05:17 ways to actually prompt the user for interactivity.
Lesson notes are only available for subscribers.