microsoft access assign macro to button

All-Access Subscriptions
Business Licensing
Lightroom Classic
Photoshop Elements
Crystal Reports
Interview Skills
Resume Skills
Excel for Lawyers
Outlook for Lawyers
QuickBooks for Lawyers
Word for Lawyers
Microsoft Office Suite
Outlook on the Web
Microsoft Project
Microsoft Teams
QuickBooks Online
QuickBooks Pro
Video Course Options
SUBSCRIPTIONS
Photoshop Elements 2023
Photoshop Elements 2022
Photoshop Elements 2021
Photoshop Elements 2020
Photoshop Elements 2019
Photoshop Elements 2018
Photoshop Elements 15
Google Classroom for Teachers
Google Docs
Google Drive
Google Forms
Google Sheets
Google Slides
Mac OS Ventura-Mojave Keyboard Shortcuts
Access for Office 365
Excel for Office 365
Microsoft Office 365
Outlook for Office 365
PowerPoint for Office 365
Publisher for Office 365
Word for Office 365
Access 2021
Microsoft Office 2021
Outlook 2021
PowerPoint 2021
Publisher 2021
Access 2019
Microsoft Office 2019
Outlook 2019
PowerPoint 2019
Publisher 2019
Access 2016
Microsoft Office 2016
OneNote 2016
Outlook 2016
PowerPoint 2016
Publisher 2016
Microsoft Office 2013
OneNote 2013
Outlook 2013
PowerPoint 2013
Microsoft Office for iPad
QuickBooks Pro 2023
QuickBooks Pro 2022
QuickBooks Pro 2021
QuickBooks Pro 2020
QuickBooks Pro 2019
QuickBooks Pro 2018
QuickBooks Pro 2017
QuickBooks Pro 2016
QuickBooks Pro 2015
QuickBooks Pro 2014
Sage 50 2019
Sage 50 2018
Windows 11 and 10 Keyboard Shortcuts
Chrome OS for Chromebooks
Mac OS Ventura
Mac OS Monterey-Mojave
Word and Excel (Mac) 2022
Word and Excel (PC/Windows) 2021-2016 and 365
QuickBooks Desktop (PC/Windows) 2023
QuickBooks Desktop (PC/Windows) 2022-2015
Windows 11 and 10
Our History
Customer Service
Testimonials
Lost Password
Register a Product
Manage My Account
Member Login

Try the Access Course for Free!

Add a macro to a button in access – instructions.

A picture showing how to add a macro to a button in Access.

Overview of Adding Macros to Buttons in Access Forms

You can add a macro to a button in Access forms to run the macro when a user clicks the form’s button. To run macros in Access forms, you often assign a macro to an “event” associated with a command button control in a form. Whenever the associated button’s “event” occurs, the macro is then run, or executed. Most often, macros are associated with the “click” event of a command button. This lets the user run the macro by clicking the button.

When adding macros to form button in Access, you can either add a standalone macro , which exists as a separate object in the database, or add an embedded macro, which only exists within the form.

How to Add a Standalone Macro to a Button in Access

To add a command button control to a form that a user can click to run a standalone macro in Access, first create the standalone macro to run. Then open the desired form to which to add the button and attach the macro in form design view.

Next, ensure you enable the “Use Control Wizards” button by clicking the “More” arrow in the lower-right corner of the scrollable list of controls in the “Controls” button group on the “Form Design” contextual tab in the Ribbon to show its drop-down menu. Ensure the “Use Control Wizards” button is highlighted, which means it is enabled. If not highlighted, click the button in the drop-down menu to enable it. This makes it much easier to create a command button.

Next, click the “Button” control in the scrollable list of controls in the “Controls” button group on the “Form Design” contextual tab in the Ribbon. Then click and drag over the area in the form over which to place the command button. When you release your mouse button, the command button is added to the form and the “Command Button Wizard” opens.

In the first screen of the “Command Button Wizard” dialog box, select the “Miscellaneous” choice from the “Categories” list. Then click the “Run Macro” choice in the “Actions” list to the right. Then click the “Next >” button to continue. In the next screen, click to select the name of the standalone macro to attach. Then click the “Next >” button to continue.

A picture showing how to add a macro to a button in Access.

To set the command button’s face, select either the “Text” or “Picture” option button. Then enter the button’s text in the adjacent field or select the icon to include on the button’s face using the adjacent options. Then click the “Next >” button to continue. In the final screen, type a name for the command button into the field provided and click the “Finish” button to add the button to the form.

How to Add an Embedded Macro to a Button in Access

Using the “Command Button Wizard” in Access is a very quick and easy way to assign a macro to a command button in a form. However, note the other options for quickly creating buttons for common macro events in the “Categories” and “Actions” lists in the first screen of the “Command Button Wizard” dialog box. Instead of embedding a standalone macro, as we just saw, you can also create embedded macros that perform the single actions listed by the choices in these two lists, if desired.

To add an embedded macro for a single, common action to an Access form, add the command button to the form using the “Command Button Wizard,” as shown above. Then just select the desired action choices in the “Category” and “Action” lists and continue to click the “Next >” button, answering questions, until you finish. Doing this lets you create quick command buttons that contain embedded macros, without needing to separately create standalone macros.

Instructions on How to Add a Macro to a Button in Access

How to Add an Embedded Macro for Common Actions to a Button in Access

Video Lesson on How to Add a Macro to a Button in Access

The following video lesson, titled “ Assigning Macros to a Command Button ,” shows you how to add macros to a button in an Access form. This video lesson is from our complete Access tutorial , titled “ Mastering Access Made Easy v.2019 and 365 .”

About Joseph Brownell

What you can read next.

A picture showing how to remove color in Photoshop Elements.

Remove Color in Photoshop Elements – Instructions

Name an Embedded Chart in Excel – Instructions

Use the Rulers in Publisher- Instructions

Rename List Items in QuickBooks Desktop Pro – Instructions

Enter Vendor Credits in QuickBooks Desktop Pro- Instructions

Transfer Funds in QuickBooks Desktop Pro – Instructions

Use a command button to start an action or a series of actions

You use a command button on an Access form to start an action or a set of actions. For example, you can create a command button that opens another form. To make a command button perform an action, you write a macro or event procedure and attach it to the command button's On Click property. You can also embed a macro directly into the On Click property of the command button. This makes it possible to copy the command button to other forms without losing the functionality of the button.

What do you want to do?

Add a command button to a form by using a wizard, create a button by dragging a macro to a form, create a command button without using a wizard, customize a command button.

By using the Command Button Wizard, you can quickly create command buttons that do a variety of tasks, such as closing the form, opening a report, finding a record, or running a macro.

Right-click the form in the Navigation Pane, and then click Design view on the shortcut menu.

On the Design tab, in the Controls group, click Button .

In the design grid, click where you want the command button to be inserted.

The Command Button Wizard starts.

Follow the directions in the wizard. On the last page, click Finish .

The wizard creates the command button and embeds a macro in the button's On Click property. The macro contains actions that perform the task you chose in the wizard.

View or edit a macro embedded in a command button

Click the command button to select it, and then press F4 to display its property sheet.

The Macro Builder is displayed, showing the action or actions that make up the embedded macro.

Top of Page

If you have already created and saved a macro, you can easily create a command button that runs the macro by dragging the macro from the Navigation Pane to a form that is open in Design view.

In the Navigation Pane, locate the macro that you want the new command button to run, and then drag the macro to the form.

Access automatically creates a command button and uses the macro name as the button's caption. Access also inserts the macro name in the On Click property of the command button so that the macro runs when you click the button. Access uses a generic name for the button, so it is a good idea to type a more meaningful name in the button's Name property. To display the property sheet for the command button while the form is open in Design view, click the button, and then press F4.

For more information about creating macros, see the article Create a user interface macro .

You can create a command button without using the Command Button Wizard. The process involves placing the button on the form and then setting a few properties.

On the Design tab, in the Controls group, ensure that Use Control Wizards is not selected.

Click the location on the form where you want to place the command button.

Access places the command button on the form.

Because Use Control Wizards was not selected, Access does not perform any further processing. If the Command Button Wizard starts when you place the command button on the form, you can click Cancel in the wizard.

With the command button selected, press F4 to display its property sheet.

Click the All tab to display all the properties for the command button.

Set the properties to complete the design of the command button, as shown in the following table.

For help with other properties not mentioned here, place the cursor in a property box and press F1.

Access provides many ways to customize command buttons so that you can have the appearance and functionality you want on your form. For example, you can create a row of command buttons in a tabular or stacked arrangement, or you can make command buttons appear more like hyperlinks.

Do one of the following:

Create a horizontal (tabular) or vertical (stacked) layout of command buttons

You can add command buttons to a tabular or stacked layout to create a row or column of precisely aligned buttons. You can then reposition the buttons as a group, which makes it easier to modify forms. You can also apply formatting styles to the entire row or column of command buttons at the same time, and you can separate the buttons by using grid lines.

Right-click one of the command buttons that you want to add to the layout, and then point to Layout on the shortcut menu.

Access draws borders around the command button to indicate the layout. Each button is paired with a label that you cannot delete — however, you can shrink the label to a very small size, if it is not needed.

The following illustration shows a command button and its associated label in a tabular layout. Note how the label is in the next higher section — this keeps the labels from repeating for each detail record.

The following illustration shows a command button and its associated label in a stacked layout. In a stacked layout, the label and the button are always in the same section.

Add more command buttons to the layout by dragging them to the layout area. As you drag a command button over the layout area, Access draws a horizontal insertion bar (for a tabular layout) or a vertical insertion bar (for a stacked layout) to indicate where the command button will be placed when you release the mouse button.

When you release the mouse button, the control is added to the layout.

Move command buttons up a section or down a section within a tabular layout If you want to keep a command button in a tabular layout but want to move the command button to a different section of the form (for example, from the Detail section to the Form Header section), do the following:

Select the command button.

Click either Move Up or Move Down on the Arrange tab.

The command button moves up or down to the next section, but remains within the tabular layout. If there is a control already in the position that you are moving this control to, the two controls exchange places.

Note: The Move Up and Move Down commands are disabled for stacked layouts.

Move an entire layout of command buttons

Click any of the command buttons in the layout.

A layout selector appears at the upper left corner of the layout.

Drag the layout selector to move the layout to a new location.

Add gridlines to a layout of command buttons

Select any of the command buttons in the layout.

Click the Gridlines command on the Arrange tab, and then click the style of gridlines you want.

Make a command button transparent

By making a command button transparent, you can place it over any object on your form and give that object the functionality of a command button. For example, you have an image that you want to divide into separate, clickable areas, each of which starts a different macro. You can do this by placing multiple, transparent command buttons on top of the image.

Click the command button you want to make transparent, and then press F4 to display the command button's property sheet.

On the Format tab of the property sheet, click in the Transparent property box.

Select Yes in the drop-down list.

You can still see the outline of the command button in Design view, but the button is invisible in Form view.

Note: Setting a command button's Transparent property to Yes is not the same as setting its Visible property to No . Both operations hide the command button, but setting the Transparent property to Yes leaves the button enabled. Setting the Visible property to No disables the button.

Make a command button appear as a hyperlink

If you want, you can hide a command button but leave the caption visible. The result is something that looks like a label, but functions as a command button. You can also underline the text in the caption and change its color to make it appear as a hyperlink.

Click the command button to select it and press F4 to display its property sheet.

On the Format tab of the property sheet, click in the Back Style property box.

In the drop-down list, select Transparent .

The body of the command button is hidden, but its caption remains visible.

To underline or change the color of the text in the caption, use the tools in the Font group on the Design tab.

Commands available in the Access Font group

Create a Cancel button

Click the command button, and then press F4 to open its property sheet.

In the Cancel property box, click Yes .

When a command button's Cancel property is set to Yes , and the form is the active form, a user can select the command button by clicking it, pressing the ESC key, or pressing ENTER when the command button has focus. When the Cancel property is set to Yes for any one command button, that property is automatically set to No for all other command buttons on the form.

To make a Cancel button cancel all the actions that have taken place in a form or dialog box, you need to write a macro or event procedure and attach it to the On Click property of the button.

Note: For a form that allows irreversible operations (such as deletions), it is a good idea to make the Cancel button the form's default command button. To do this, set both the Cancel property and the Default property to Yes .

Click Browse to find the picture that you want to use or, alternatively, click one of the pictures in the Available Pictures list to preview the professionally-created pictures that you can use. If you find a picture you want, click OK to add it to the command button.

By default, Access sets the Picture Type property to Embedded . When you assign a graphic to a command button's Picture property, using this setting creates a copy of the picture and stores it in the Access database file. Any subsequent changes to the original picture will not be reflected in the command button. To create a link to the original picture so that any changes to the picture will be reflected in the command button, change the Picture Type property to Linked . You must keep the original picture file in its original location. If you move or rename the picture file, Access displays an error message when you open the database, and the command button displays its caption instead of the picture.

Display both a picture and a caption on a command button

You can display both a caption and a picture on a command button. Use the following procedure:

Add a picture to the command button by using the procedure outlined earlier in this section.

Select the command button. If the property sheet is not already displayed, press F4 to display it.

On the Format tab of the property sheet, type the caption you want in the Caption property box.

Click the drop-down arrow in the Picture Caption Arrangement property box, and then select the arrangement you want. For example, to display the caption below the picture, select Bottom . To display the caption to the right of the picture, select Right . To make the arrangement dependent on the system locale, select General . By using this setting, the caption will appear on the right for languages that read from left to right, and it will appear on the left for languages that read from right to left.

Need more help?

Want more options.

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Microsoft 365 subscription benefits

Microsoft 365 training

Microsoft security

Accessibility center

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Ask the Microsoft Community

Microsoft Tech Community

Windows Insiders

Microsoft 365 Insiders

Was this information helpful?

Thank you for your feedback.

Database.Guide

How to add a macro button to a form in access.

To add a macro to a form in Access 2013 or 2016:

Below are screenshots for the above steps.

Create a Macro

Here’s an example of a macro that runs a query. If you don’t know how to create macros, see how to create a macro in Access .

Open the Form

Now, open the form in Design view.

Click the Button Icon

Click the Button icon in the Ribbon (from the DESIGN tab):

Screenshot of the button icon on the Ribbon in Access 2013.

Add the Button to the Form

In the Form Detail area, click and drag the cursor so that it forms a square or rectangle (i.e. shape of a button). The Command Button Wizard will pop up. Select Miscellaneous > Run Macro and click Next :

Screenshot of Command Button Wizard in Access 2013.

Select the Macro

Select the name of the macro you want to run and click Next .

Text or Image Button?

Choose whether to display text or an image on the button and click Next .

Name the Button

Give the button a name and click Finish .

Screenshot of form in Design view with macro button.

The user can now run the macro by clicking on the button.

How to Run a Macro from a Navigation Button in Microsoft Access

You're not limited to running forms and reports from the navigation form. You can use navigation buttons to run macros as well, thus opening the range of functionality to any process you can write a macro or module for.

This feature works the same in all modern versions of Microsoft Access: 2010, 2013, and 2016.

Microsoft Access Forms – Parameter Queries, Macros & Buttons

You are here, microsoft access forms – parameter queries, macros & buttons, adding buttons & macros to an access form.

Adding a Button to an Access Form using the Command Button Wizard Adding an OpenQuery Macro to an Access Form

Adding a Button to an Access Form using the Command Button Wizard

Click Finish

To test your button, view your form in Form View and click on the button. (There is an example picture of the Form with a button below.)

Adding an OpenQuery Macro to an Access Form

There are many reasons why you may wish to add a button to an Access form, and this is the first in a number of examples that we will go through.

One reason you might want a button, is to run queries, so that you can view your data in different ways. Not only that, but when you run these queries, you want the data to be dynamically filtered. I was recently asked how to do this:

You can create multiple queries to manipulate all the data you wish, and then in the criteria section of the specific field you wish to filter add a parameter, so that the user can specify what they want to be shown. Then, you can attach these queries to buttons or labels on a form, with a macro which opens queries.

Add the Parameter to the Criteria section, of the relevant field in Query Design View:

When you run the query, the parameter will appear as:

Simply type in the value you wish to see. (In the example above it would be records related to the CustomerID you wish to view)

Then to attach it to the form button:

Select the query you wish to run, and how you want them to view the data.

Once you’ve returned to the Form View , you can test your button, it should look similar to the example below. My Form is based on customer data. I created a button that will run a query, to show order details related to a specific customer.

When I click on the button, it runs the query, and asks me which customer I would like to see data related to. It then displays the data sheet you see pictured below. (Remember, you are asked which customer you wish to see, because of the Parameter you created.)

In this example you have been shown how to add parameters to a query, to enable you to dynamically manipulate the view of your data. Every time you click the button, you need to provide the query with a CustomerID. This process can be automated so that the query will run, and base its results on the CustomerID presented on the form. This will be covered in a separate blog, so keep your eyes peeled!

To learn more about Microsoft Access Forms and manipulating your data, join us on one of our Microsoft Access Training Courses .

Share this post

FMS Site Search

Running VBA Code from a Microsoft Access Macro

Background on macros.

To use combo boxes effectively, learn about the following properties:

There are many Microsoft Access users who are comfortable writing macros to automate tasks but not familiar with module code and its Visual Basic for Applications (VBA) programming language. That's okay. Everyone has to start somewhere.

The VBA programming language is used across the Microsoft Office products. In addition to Access, you can use it in Excel spreadsheets, Word documents, PowerPoint presentations and Outlook. VBA is also very similar to language used in Visual Basic 6. In fact, the syntax and module editor (Integrated Development Environment or IDE) is practically identical.

In MS Access, macros are completely different from modules. Macros and modules appear as separate objects in the database container:

Macros are structured actions that are easily created by choosing options from drop down lists. After selecting an action, it's easy to see the available options and choose appropriately.

There's no need to learn a programming language and it's very easy to automate tasks such as running queries, printing reports, opening table and forms, etc. Putting a macro in design mode shows the individual commands:

If you name a macro Autoexec, Microsoft Access automatically runs it when your database opens. You can also open Microsoft Access from a command line and specify a macro name after the /x switch to run it when it opens.

For instance, you can run the macro named MyMacro like this:

This assumes the 32-bit version of Access 2016 is located in its default location. To locate Access, use Windows Explorer and search for msaccess.exe.

For a complete list of command line switches visit this Microsoft page on Office Command Lines .

Background on VBA Modules and Procedures

VBA lets you create modules with more advanced functionality than macros. You'll need to know a little more about programming to write VBA code as it's not structured like macros since the editor lets you type anything, though Intellisense is available to simplify entering built-in commands. Modules are composed of functions and subs (procedures) which perform the tasks you define. A Function is a procedure that returns a value while a Sub performs its task without returning a value. Procedures can be passed parameters to execute different behavior based on those values.

We have many examples of procedures throughout our web site and our Total Visual SourceBook program is a commercial library with over 3600 procedures. Here are some examples .

Note that Excel doesn't have macros but often refers to VBA functions are macros.

Running a Function from a Macro

Let's create a simple function and call it from a macro.

Create a new module

From the Create ribbon, choose Module:

Add a Function

Paste this text into your module. This is a function that shows a message with the current time.

To run it, just put your cursor on the first line and press F5 to see the message box with the current time.

You can also run it from the Immediate Window by pressing Ctrl G to open the Immediate Window and typing:

In our example, the function doesn't return a value but it still needs to be a Function rather than Sub to be callable from a macro.

To call a function from a macro, it cannot be defined as a Private function. Private procedures are only visible in the module. Without "Private" it is public and visible to other objects including macros. Using Public in our example makes it explicit.

Save the Module

From the menu, select File Save and give it a name:

By default, it's Module1, but you can rename it.

Run the Function from a Macro

To run this function from a macro, simply add a RunCode action and refer to the function:

Make sure to include the () after the function name. Save the macro. When you run the macro, the function is called.

Run a Total Access Emailer Function from a Macro

Total Access Emailer is our Microsoft Access Emailing program . The add-in version of Total Access Emailer lets you create and run email blasts interactively.

The Professional Version lets you run the email blasts through VBA. The program's Code Generator lets you easily create functions and set parameters interactively so you can insert it into your projects and invoke it from events such as a button click or other VBA code.

Because it's a function, you can also run it from a macro. This page illustrates the programmatic interface and the Code Generator which creates a function named RunEmailBlast(). If you paste that into a module and change the first line from Private to Public, you can run it from a macro by calling that function name:

Additionally, you can invoke the Total Access Emailer function, TotalAccessEmailer, directly from RunCode and pass its required parameters.

For instance, this would run email blast 23 with the options specified by the other parameters as defined in the code generator page.

By creating a macro to call that function, every time you run the macro, email blast 23 is run. You can add multiple RunCode commands to run a series of email blasts.

Microsoft Access Developer Center

Strategic Overview

Microsoft Access within an Organization's Database Strategy

Microsoft Access vs. Excel

How many simultaneous Microsoft Access users?

Blaming Microsoft Access instead of the Developer

History of Access

Microsoft Access Version Feature Differences

Microsoft Access Versions, Service Packs and Updates

Microsoft Office 365 Access Update Version Releases

How Access Started

Top 14 Features Added with MS Access 2007

Best Practices

Taking Over Legacy MS Access Databases

100+ Performance Tips

Winner of Every Best Access Add-in Award

Split Database Architecture for Multiuser

Find and Delete Unused Objects and VBA Code

Table Design

Design Primary Keys

Field Type Consistency

Table Normalization Tips

Take Time into Account

Temporary Data

Transpose Data

Set AutoNumber Starting Number Other than 1

Avoid Unnecessary or Duplicate Indexes

Replace Attachment Field Paperclip Icon

Avoid Decimal Data Types

Query Design

Microsoft Access Query Tips and Techniques

Form Design

Form Tips and Mistakes

Copy Command Button and Keep Picture

Module VBA to Forms and Controls

Form Navigation Caption

Resync Record in a Subform

Synchronize Two Subforms

Multiple OpenArgs Values

Late Bind Tab Subforms

Subform Reference to Control Rather than Field

Tab Page Reference

Shortcut Keys

Combo Box Top 6 Tips

Properties and Validation

Select First Item

Cascading Combo Boxes

Zip, City, State AutoFill

Report Design

Suppress Page Headers and Footers on the First Page of Your Report

Add the NoData Event

Annual Monthly Crosstab Columns

Design Environment

Add Buttons to the Quick Access Toolbar

Collapse the Office Ribbon for more space

VBA Programming

Basics: Forms and Controls

Run VBA Code from a Macro

Use Nz() to Handle Nulls

Avoid Exits in the Body of a Procedure

Shortcut Debugging Keys

Set Module Options

Math Rounding Issues

Rename a File or Folder

Avoid DoEvents in Loops

Age Calculations

Weekday Math

Send Emails with DoCmd.SendObject

Source Code Library

Microsoft Access Modules

VBA Error Handling

Error Handling and Debugging Techniques

Error Number and Description Reference

Basic Error Handling

Pinpointing the Error Line

Performance Tips

Disable Design Changes

Broken References

Remote Desktop Connection Setup

Terminal Services and RemoteApp Deployment

Reboot Remote Desktop

Missing Package & Deployment Wizard

Avoid Program Files Folder

Unavailable Mapped Drives

System Admin

Disaster Recovery Plan

Compact Database

Compact on Close

Database Corruption

Remove 'Save to SharePoint Site' Prompt from an Access Database

Class Not Registered Run-time Error -2147221164

Inconsistent Compile Error

Decompile Database

Bad DLL Calling Convention

Error 3045: Could Not Use

Converting ACCDB to MDB

SQL Server Upsizing

Microsoft Access to SQL Server Upsizing Center

When and How to Upsize Access to SQL Server

SQL Server Express Versions and Downloads

Cloud and Azure

Cloud Implications

MS Access and SQL Azure

Deploying MS Access Linked to SQL Azure

SQL Server Azure Usage and DTU Limits

Visual Studio LightSwitch

LightSwitch Introduction

Comparison Matrix

Additional Resources

Connect with us.

Celebrating our 35th Year of Software Excellence

MS Access Products

Total Access Admin

Total Access Analyzer

Total Access Components

Total Access Detective

Total Access Statistics

Access/Office/VBA and VB6 Products

Total Visual Agent

Total Visual CodeTools

Total Visual SourceBook

Zip Code Database

Product Catalog

Forum & Ticket Submissions

Support Options

Product Updates

Non-Technical Questions

Location and Directions

International Dealers

Consulting Services

How to run macro in Excel and create your own macro button

In this tutorial, we'll cover many different ways to run a macro in Excel - from the ribbon and VB Editor, with a custom keyboard shortcut, and by creating your own macro button.

Though running an Excel macro is a simple thing for experienced users, it might not be immediately obvious to beginners. In this article, you will learn several methods to run macros, some of which may completely change your way of interacting with Excel workbooks.

How to run a macro from Excel ribbon

One of the fastest ways to execute VBA in Excel is to run a macro from the Developer tab. If you have never dealt with VBA code before, you may need to activate the Developer tab first. And then, do the following:

Tip. If the Developer tab is not added to your Excel ribbon, press Alt + F8 to open the Macro dialog.

Run a macro with custom keyboard shortcut

If you execute a certain macro on a regular basis, you can assign a shortcut key to it. A shortcut can be added while recording a new macro and to an existing one. For this, carry out these steps:

On the Developer tab, in the Code group, click Macros .

Click Options to edit the macro parameters.

For lowercase letters, the shortcut is Ctrl + letter .

Close the Macro dialog box.

Tip. It is recommended to always use uppercase key combinations for macros ( Ctrl + Shift + letter ) not to override the default Excel shortcuts. For example, if you assign Ctrl + f to a macro, you will lose the ability to call the Find and Replace dialog.

Once the shortcut is assigned, simply press that key combination to run your macro.

How to run macro from VBA Editor

If you aim to become an Excel pro, then you should definitely know how to start a macro not only from Excel, but also from the Visual Basic Editor. The good news is that it's a lot easier than you might expect :)

Alternatively, you can use one of the following shortcuts:

Tip. If you like operating Excel from you keyboard, this tutorial may come in handy: 30 most useful Excel keyboard shortcuts .

How to create a macro button in Excel

The traditional ways of running macros are not hard, but still might present a problem if you are sharing a workbook with someone who has no experience with VBA - they simply won't know where to look! To make running a macro really easy and intuitive for anyone, create your own macro button.

Click anywhere in the worksheet. This will open the Assign Macro dialogue box.

If the text does not fit in the button, make the button control bigger or smaller by dragging the sizing handles. When finished, click anywhere on the sheet to exit the edit mode.

Tip. You can also assign a macro to an existing button or other Form controls such as spin buttons or scrollbars. For this, right-click the control inserted in your worksheet and choose Assign Macro from the pop-up menu.

Create a macro button from a graphic object

Regrettably, it is not possible to customize the appearance of button controls, because of which the button we created a moment ago does not look very nice. To make a really beautiful Excel macro button, you can use shapes, icons, images, WordArt and other objects.

As an example, I'll show you how you can run a macro by clicking a shape:

How to add a macro button to Quick Access Toolbar

The macro button inserted in a worksheet looks good, but adding a button to each and every sheet is time-consuming. To make your favorite macro accessible from anywhere, add it to the Quick Access Toolbar . Here's how:

Click OK twice to close both dialog windows.

How to put a macro button on Excel ribbon

In case you have a few frequently used macros in your Excel toolbox, you may find it convenient to have a custom ribbon group, say My Macros , and add all popular macros to that group as buttons.

First, add a custom group to an existing tab or your own tab. For the detailed instructions, please see:

And then, add a macro button to your custom group by performing these steps:

Click OK to save your changes and close the main dialog box.

How to run a macro on opening a workbook

Sometimes you may want to run a macro automatically on opening a workbook, for example, to display some message, run script or clear a certain range. This can be done in two ways.

Run macro automatically by using Workbook_Open event

Below are the steps to create a macro that automatically runs whenever you open a specific workbook:

For example, the following code will display a welcome message each time the workbook is opened:

Trigger macro on workbook opening with Auto_Open event

Another way to run a macro automatically on workbook opening is by using the Auto_Open event. Unlike the Workbook_Open event, Auto_Open() should sit in a standard code module, not in ThisWorkbook .

Here are the steps to create such a macro:

A macro runs automatically whenever the workbook is opened.

Here's an example of the real-life code that displays a message box on workbook opening:

Note! The Auto_Open event is deprecated and available for backwards compatibility. In most cases, it can be replaced with the Workbook_Open event. For more information, please see Workbook_Open vs. Auto_Open .

The message box is displayed every time you open the workbook.

Now that you know lots of ways to run a macro in Excel, you just need to choose the one best suited for your needs. I thank you for reading and hope to see you on our blog next week!

microsoft access assign macro to button

IMAGES

VIDEO

COMMENTS