Running Macros: Using Buttons

Quick reference

• 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.