Use Macros with UserForms

Add to Favorites
Author: don

This tutorial explains how macros interact with UserForms.

This includes an explanation of how to call and use a macro that is in a module and also how macros in modules can call and use UserForms.

Sections:

Use a Macro in a Module from a UserForm

Do Something with a UserForm from a Macro

Notes

Use a Macro in a Module from a UserForm

The code behind a UserForm is basically the same as any other macro and this code can use and interact with macros within Excel. You can call and interact with a macro just like you would from any other macro.

Let's create a simple macro within a module and then call that macro from the UserForm.

Simple Macro

Sub MacroInModule()

MsgBox "Hi!"

End Sub

Place this in a regular module (Insert > Module) like this:

e8f2c07b132935df611bd9e975d8e5f8.jpg

This macro will simply display a pop-up window that says "Hi!" This will allow you to have visual confirmation that a macro was called from the UserForm.

Call the Macro from a UserForm

In a UserForm, we call or use the above macro just like we would from any other macro, using the Call feature.

First, decide what will make the UserForm call the above macro; you can have it run via a button click or from anywhere else in the VBA code for the form.

I'm going to create a new button called Run Macro and then have the above macro run when this button is clicked.

To do this, add a new button control to the form:

5a1e3daa5ff2ef749c1238bd7f8db56b.png

Double-click the button and you will then see the code window for the UserForm appear:

1ffb9b5382426702fd1b99fbdfb0c015.png

At the top of the code window will be the section for the button that was just created.

In that section, put the code to call the macro:

Call MacroInModule

aca336b7721bc40ddee1d5767787dc1e.png

This is just Call and then the name of the macro that we want to run.

Now, when we run the UserForm we can click the button Run Macro and the macro in the module will run and display a pop-up window that says "Hi!"

a95f5472f0f66f42d7bbec76949f7ed7.jpg

You can see the small window that appears over the form; this confirms that the macro was successfully called from the module.

As you can see, it's very easy to use macros that are not specifically contained within the code section of the UserForm.

Here are a couple tutorials we have on how to call and interact with macros from other macros and they apply to this tutorial as well.

Run a Macro from Another Macro in Excel

Pass Values from One Macro to Another Macro

Do Something with a UserForm from a Macro

You can interact with forms from macros simply by referencing the desired macro.

This is most clearly visible when you need to open or run the UserForm; to do this, you put a small piece of code within a regular macro in a module and then you call that macro.

Here is the code for that:

Sub ShowUserForm()

UserForm1.Show

End Sub

a4cf04c92930d3ed93bed630237119a8.png

UserForm1 is the name of the UserForm that you want to interact with.

Show tells Excel to literally open and show the UserForm.

There are many things that you can do with a UserForm from a macro that is in a module, but this example is meant merely to show you how easy it is and to show you that a UserForm is not some completely separate entity that cannot be referenced by code from other parts of Excel.

Notes

Most of the time, macros that are placed within modules or worksheets are called from UserForms that are already open; the most common thing that a macro within a module does with a UserForm is to open it.

This may sound confusing, but, just remember, this tutorial is meant to help you understand that the code wtihin an Excel workbook can work together and interact with each other, whether it is in a module, worksheet, or a UserForm.

Make sure to download the attached sample file to see these examples in Excel.

Question? Ask it in our Excel Forum


Downloadable Files: Excel File

Similar Content on TeachExcel
What is a Macro in Excel?
Tutorial: This is the first step to learning about Macros for Excel and how to use them.  What is a ...
Logical Operators in Excel VBA Macros
Tutorial: Logical operators in VBA allow you to make decisions when certain conditions are met. They...
Get the Name of a Worksheet in Macros VBA in Excel
Tutorial: How to get the name of a worksheet in Excel using VBA and Macros and also how to store tha...
Create a Column Chart with a Macro in Excel
Macro: This macro adds a column chart to Excel. This is an easy to use macro that allows you to q...
Add a Drop Down Menu to a Cell in Excel - Data Validation Macro
Macro: Add a drop down menu or list to a cell in Excel with this free Excel macro. This is a grea...
Guide to Making Your Macro Run Faster and Better in Excel
Tutorial: This guide will show you 4 different ways to make your macros run faster and more efficien...
Tutorial Details
Downloadable Files: Excel File
Similar Content
What is a Macro in Excel?
Tutorial: This is the first step to learning about Macros for Excel and how to use them.  What is a ...
Logical Operators in Excel VBA Macros
Tutorial: Logical operators in VBA allow you to make decisions when certain conditions are met. They...
Get the Name of a Worksheet in Macros VBA in Excel
Tutorial: How to get the name of a worksheet in Excel using VBA and Macros and also how to store tha...
Excel Forum