Introduction to VBA Userform.Show
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.
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.
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
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
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
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
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:
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.