🥳 GOSKILLS TURNS 10: Get 10 days of free access with code 10YEARS

GoSkills
Help Sign up Share
Back to course

Running Macros: Using Buttons

Quiz me Quiz Compact player layout Large player layout
Focus video player for keyboard shortcuts
Auto
  • 720p
  • 540p
  • 360p
1.00x
  • 0.50x
  • 0.75x
  • 1.00x
  • 1.25x
  • 1.50x
  • 1.75x
  • 2.00x
cc

We hope you enjoyed this lesson.

Get the Microsoft Excel - Macros and VBA course for more great video tutorials.

Start free trial

Cool lesson, huh? Share it with your friends

Facebook Twitter LinkedIn WhatsApp Email

  • Lesson resourcesResources
  • Quick referenceReference
  • Transcript
  • Notes

About this lesson

How to run your macro from a worksheet button.

Exercise files

Download this lesson’s related exercise files.

Running Macros: Using buttons
23.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
Login to download
  • 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.

Running Macros: Using Keyboard Shortcuts
05m:05s
Navigating the Visual Basic Editor
05m:21s
Share this lesson and earn rewards

Facebook Twitter LinkedIn WhatsApp Email

Gift this course
Give feedback

How is your GoSkills experience?

I need help

Your feedback has been sent

Thank you

Back to the top

© 2023 GoSkills Ltd. Skills for career advancement