Make a UserForm in Excel

Add to Favorites
Author: don | Edits: don

Let's create a working UserForm in Excel.

This is a step-by-step tutorial that shows you everything you need to do to design, build, run, open, and close a basic UserForm in Excel. After reading this tutorial, you should have a basic understanding of how to create and use a UserForm.

Download the sample file if you want to see the finished product in Excel, or simply follow along below.

e83af1ad0f7321dcae681fcfbe1015f8.jpg

Sections:

How to Make a UserForm

Make the UserForm Work

Notes

How to Make a UserForm

  1. Hit Alt + F11 to go to the VBA window. Then go to InsertUserForm
    c0c9b3c81648768acaabd5adbb4aedde.jpg
    You should now see something like this:
    9472be79c417e73c821c7362fd2388d2.png
    On the right, we have the blank UserForm and we can now begin to build it.
  2. Edit the Title of the UserForm. Go to the Properties window on the bottom left and look to the line that says Caption and change the text on the right to whatever you want. (If you don't see the Properties window, make sure you have selected/clicked the UserForm; you can also go to ViewProperties Window or hit F4 on the keyboard)
    742fe9a91ffe30f3819592ab637ca641.png
    You should now see the title of the UserForm has changed too.
    1c3aa6fb44430250a52f37512ec68ca4.jpg
  3. Add some text to the UserForm. Click the UserForm and the Controls Toolbox should appear - if it doesn't, go to the menu ViewToolbox and it will appear.
    Everything that you can add to a UserForm is called a "control."
    In the Toolbox, click the A (Label) 
    a4f91e457b9b389c4dfd91a286c33da9.png
  4. Move your mouse to the UserForm and click and drag it to create a section where the Label will appear. Once you release the mouse button, the Label will appear on the form.
    d26a6aec55d07fe74203d4adbe0fffc9.png
    a7a950a304d251e5d9244cc605730f32.png
  5. With the label still highlighted, go to the Properties window and next to where it says Caption enter the text that you would like to appear in the label.
    79ca352551c233383671f8e001fdb606.png
  6. To change the appearance of the text, click the Font section in the Properties window and then click the small button with 3 dots that appears.
    55a6fec374844f7b7e79d2c59bfaaefe.jpg
    From this window, you can change the color of the text, its size, and its appearance.
    a612934bcd9a84b901eb1a333ff89af7.jpg
  7. Add a textbox so the user can enter something into the UserForm.
    Click the TextBox button from the Controls box and then move your mouse over the UserForm and click and drag to size and then insert the TextBox; release the mouse button to put it into the form.
    83e1318e86134242472f26cab4e8b02f.jpg
    d7559fd8446cc98ec337c6b3ed3b0e1c.png
  8. Repeat step 7 for a CommandButton - these are the buttons that you click on the form.
    0eb9a62f1b022fe50ae65f226234bef5.jpg
    54946ce9ebac05c827c8b78ef2b9b048.png
  9. To change the text that appears in the buttons, click the button on the form and edit the Caption section in the Properties window.
    772acfdd36356aefb10d113183f338a5.jpg

Now, we have created a very basic and simple UserForm.

If you view it in Excel, it looks like this:

48805c770749f5005c639855293e8e81.jpg

It's a nice UserForm, but it doesn't actually do anything yet.

In the next section, we will add some functionality to the UserForm and make everything work.

Make the UserForm Work

Display the UserForm

Method 1

Go to the VBA window (Alt + F11) and select the UserForm and then hit F5. This will launch the UserForm in Excel. From there, you can close the window by clicking the X in the upper-right corner of the window.

Method 2

Method 1 is not practical for a regular user of Excel. To launch the UserForm in a more intuitive way, we need to make a small macro and attach it to a button in Excel.

Here is the code that we need:

Sub ShowUserForm()

UserForm1.Show

End Sub

UserForm1 is the name of the UserForm - you see this when you click the UserForm in the VBA window and then look to the (Name) section of the Properties window.

Put the above macro into a Module - VBA window (Alt + F11) > Insert > Module.

948a3b3425100a01b48aa0969be71778.png

You can run the above macro any way you like; I prefer to attach the macro to a button in Excel that will then show the UserForm when it is clicked.

d764d896451e1d4dcb5cc30ead1d2db4.png

Close the UserForm

In the VBA window (Alt + F11), go to the UserForm (if you don't see it, just double-click it from the ProjectWindow on the left) and double-click the button that will cause the UserForm to be closed.

Once you double-click the button, you should see a window open that looks like this:

48d62c1191c98999332ebebd15b060a8.png

Put this piece of code in there:

Unload Me

44bfbe93f39b15565cbe5cd08d141bad.png

Now, when you have the UserForm open, just click this command button and it will close.

In my example, the Close Window button closes the UserForm. The other button still doesn't do anything yet.

Get a Value from the User and Put it into Excel

Let's take the input from the TextBox in the UserForm and have it placed into the spreadsheet when the user clicks the button Show Text.

  1. Get the name of the TextBox; we use the name to reference the text box in order to get the value that the user will type into it.
    Click the Textbox and look to the (Name) section of the Properties window.
    2f78138b98c9cfaf70007ef7aa49d51a.jpg
  2. Double-click the Show Text button, the button that we want to use to cause something to happen with the text; you should now see the code window and your cursor blinking in a new section of the code.
    2d99bdd7a91dccf745b7fce4504af371.png

  3. In the window that opens, tell the UserForm to do whatever we want to do with the value that is put into the text box.
    Here, I'll tell it to put this value into cell A1 of the worksheet named "Sheet1"
    Sheets("Sheet1").Range("A1").Value = TextBox1.Text
    In the above code, TextBox1 is the name of the TextBox that we got from step 1.
    9d2df2ebdbe41f9847468495fee1b96c.png
    Note: once you double-click the button and see this code window, you will see all of the code that is specific to any event within the UserForm. This means that you will see the code that runs when the other command button is clicked, "Unload Me". An event is when something happens or is done to a UserForm.
    There may be many pieces of code on this screen and you will put your code into the section that contains the blinking cursor after you double-clicked the button.
  4. Run the UserForm now and test it out!

Here is the final result:

b77d9ea27233dde90656a1c0acc41045.jpg

Once you click the Show Text button, whatever was typed into the TextBox will now appear in cell A1.

Notes

As you can see, creating a fully functioning UserForm is really not difficult and can be done in less than an hour for anyone who simply reads this tutorial. Now, there are a lot of other ways to display and work with data in UserForms, but this tutorial should get you up and running with the basic concept and a basic working example.

Make sure to download the sample file for this tutorial to see the working example in Excel.

Question? Ask it in our Excel Forum


Downloadable Files: Excel File

Similar Content on TeachExcel
What is a UserForm in Excel?
Tutorial: A UserForm is basically a pop-up window that you can use to create a custom interface for ...
Closing a UserForm
Tutorial: How to close a UserForm in Excel. This includes hiding the form as well as completely clos...
Showing a UserForm
Tutorial: How to display a UserForm in Excel and some common things you might want to do when you di...
Get Day Name from a Date in Excel
Tutorial: How to get the name of a day from a date in Excel. This returns, for example, "Tuesday" fo...
Add Image Background to a Chart in Excel
Tutorial: How to use an image for the background of a chart in Excel. Simple but fun way to spruce...
Print Only Specific Parts of a Worksheet in Excel
Tutorial: In Excel you can select parts of a worksheet to print while ignoring all of the other data...
Tutorial Details
Downloadable Files: Excel File
Similar Content
What is a UserForm in Excel?
Tutorial: A UserForm is basically a pop-up window that you can use to create a custom interface for ...
Closing a UserForm
Tutorial: How to close a UserForm in Excel. This includes hiding the form as well as completely clos...
Showing a UserForm
Tutorial: How to display a UserForm in Excel and some common things you might want to do when you di...
Excel Forum