Use Macros with UserForms

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:

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:

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

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

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!"

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

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.