VBA Show Userform with Show Method

Introduction to VBA Userform.Show | How to Show a VBA UserForm | The Modal Option | Calling a Userform from a Userform | Run Macros from Userforms | Unloading your Userform | Final Thoughts

Introduction to VBA Userform.Show

The VBA Userform.Show method does exactly what one would expect: it displays the userform on the screen. Like the Application.InputBox, userforms are great for collecting user input. Userforms are far more flexible than the InputBox so you can have all sorts of interactions with users. Without showing the form, though, the user cannot interact at all!

By implementing userforms in your program, you can guide your users and interactively gather user input, like picking from a list or typing a filename. You can use Userforms to call other Userforms and even run subroutines. One useful application might be directing the user to choose a text file and inputting which words to extract and count.

How to Show a VBA UserForm

Let’s start with a userform named Userform1 (this is the default name when you create a userform). I added three basic elements: a command button, a label, and a textbox so our userform is not just an empty box.

Our visually-unappealing form looks like this:

Simple Userform with a few controls

To show it to the user, put this code into a regular module:

Sub show_userform1()

Userform1.Show

End Sub

Make powerful macros with our free VBA Developer Kit

It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.

When you run this module, this box will appear:

Our simple Userform shown over the cells of the spreadsheet

Notice the form doesn’t actually do anything except exit with the X at the top. When the .Show method is called, VBA will direct the program flow to the UserForm_Initialize event. Here, you can make numerous customizations. You can edit the userform controls, like the caption on the command button. You can even call other userforms or run entire macros in the initialization phase, but your userform won’t appear until the initialization event completes.

The UserForm_Initialize event is actually my secret to fading userforms in and out and removing userform borders.

A simple caption change using the UserForm_Initialize event would look similar to this:

Private UserForm_Initialize()

Userform1.CommandButton1.Caption = "Click Me."

End Sub

Now when the .Show method is called, your userform will rewrite the default text of the Command Button. Originally the caption the of the command button read “Choose your File” in the caption button, but now it says “Click Me.”

Our simple userform now has a new command button caption

Here are the three screens where we input VBA code to control our userform:

The three windows in which our code and userform layout should appear

Notice that the .Show method is in the module while the initialization event occurs in the userform’s own code. The third window is simply where we designed the layout of our userform.

The Modal Option

When you type .Show you will see an Intellisense argument appear for [Modal]. This argument is optional and accepts vbModal or vbModeless as its input.

By default, this is set to true (vbModal) for the .Show method, which means the user cannot do anything with the application until the userform is closed. This means the user cannot change the spreadsheet or interact with previously opened userforms while your userform is open. However, you can nest userforms and call macros from within a userform without waiting for the first form to be closed.

Calling a Userform from a Userform

You may sometimes want to call a userform from another userform. To do so, place the Userform1.Show line of code in whatever Userform event you need. Let’s look at an example.

Let’s make a new userform, named UserForm2. Our UserForm2 has a single button to show UserForm1. It looks like this:

This is our very simple Userform2

The code to call Userform1 is quite simple:

Private Sub CommandButton1_Click()
Userform1.Show
End Sub

We used the CommandButton1 object and the Click event to do this, but you could use other available objects (red arrow) or another event (blue arrow). If you add more controls, you will have more objects. There are many different types of Events. A few examples are double clicks or mouse movements.

Objects are marked by the red arrow, events by the blue one.

Run Macros from Userforms

If you want to run macros from your userform, you just need to call the macro from the desired Event. Perhaps you want to get a filename for processing later? Link your command button in Userform1 to the GetOpenFilename macro we discussed before.

Use this code to fire the basic file picker:

Private Sub CommandButton1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

basic_get_user_file     'name of public macro to call

End Sub


Sub basic_get_user_file()

Dim fileStringBasic As String

fileStringBasic = Application.GetOpenFilename()

If fileStringBasic <> "False" Then
    'your code for a single file here
End If

End Sub

Now, when you move the mouse over the button, the basic_get_user_file macro will run. Normally, you want to wait until the user actually clicks the button (CommandButton1_Click), but we wanted to demonstrate a new event for you!

In this example, we put the basic_get_user_file subroutine in the code section of the UserForm, but you can call public macros from any module the same way! You can even call private macros from your UserForm, but you have to do things a bit differently.

Don't forget to Unload

Whenever you’re done with your userforms, don’t forget to unload them. The only time they are automtically unloaded is if the user presses the X in the top bar. If you don’t unload (or hide) the Userform, the form will not disappear.

It’s a good practice to add a “Close” or “Cancel” button to your userforms. Add a new Command Button to your sample UserForm and rename it something like cbCancel by editing the (Name) property (F4). Paste the following macro into the Code section (F7) of your UserForm.

Private Sub cbCancel_Click()
    Unload Me
End Sub

When the user clicks this new button, your userform will now be closed.

You can use Unload Me to unload your userform from within the userform code, itself. However, if you want to call and unload userforms from somewhere else, like a master module, you won’t be able to use Unload Me. You’ll need to replace Me with the name of your UserForm, like:

Unload UserForm1

I hope you enjoyed this introduction to VBA UserForm.Show method. In addition to teaching you how to show your VBA userforms, we also taught you other basics, like how to call userforms from an existing userform and how to run external macros from your existing userform. Layering these skills is essential for making robust and dynamic userform interfaces for your VBA programs.

If you haven’t already done so, please subscribe to my free wellsrPRO VBA Training Program using the form below, then share this article on Twitter and Facebook.