Make a UserForm in Excel

Add to Favorites
Author: | 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.



How to Make a UserForm

Make the UserForm Work


How to Make a UserForm

  1. Hit Alt + F11 to go to the VBA window. Then go to InsertUserForm
    You should now see something like this:
    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)
    You should now see the title of the UserForm has changed too.
  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) 
  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.
  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.
  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.
    From this window, you can change the color of the text, its size, and its appearance.
  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.
  8. Repeat step 7 for a CommandButton - these are the buttons that you click on the form.
  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.

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

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


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()


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.


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.


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:


Put this piece of code in there:

Unload Me


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.
  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.

  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.
    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:


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


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.

Downloadable Files: Excel File

Question? Ask it in our Excel Forum

Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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 ...
Showing a UserForm
Tutorial: How to display a UserForm in Excel and some common things you might want to do when you di...
Closing a UserForm
Tutorial: How to close a UserForm in Excel. This includes hiding the form as well as completely clos...
Make a Password Prompt Input for Excel (With Masked Input)
Tutorial: How to make a Password input prompt window for Excel and mask the input so that you can't...
Getting Data from a UserForm
Tutorial: How to get data from a UserForm in Excel, including from: text inputs (TextBox), list boxe...
5 Best Tips for Tables in Excel (To Make You a Pro!)
Tutorial: Speed up your workday with these 5 great Table Tips for Excel! These tips make it easier ...
Tutorial Details
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course