how to assign a macro to a userform button

How to Assign a Macro to a Button in Excel (Easy Guide)

• -- By Sumit Bansal

While there are many different ways to run a macro in Excel, none of those methods can be as easy and user-friendly as clicking on a button.

And for that to work, you need to assign a macro to a button first.

In this tutorial, I will show you a couple of ways to insert a button in  Excel and then assign a macro to that button (or shape). Once done, as soon as a user clicks on the button, the macro VBA code would be executed.

For the purpose of this tutorial, I will be using the below VBA macro code (which simply selects cell A1 in the active sheet and enters the text “Good Morning” in it and colors it red).

The above VBA code is placed in a regular module in the VB Editor

Now let’s dive right in and see how you can assign this macro to a button or shape in Excel!

This Tutorial Covers:

Insert a Shape and Assign Macro to that Shape

While there are dedicated buttons that you can insert in the worksheet and then assign the macro to it, I will first cover how to assign a macro to a shape .

I personally love this method and prefer it over the rest two methods covered later. You can easily insert a shape (square or rectangle) and can make it look like a button.

And since it’s a shape, you can easily format it to look perfect with your existing formatting or brand colors.

Below are the steps to insert a shape in Excel:

• Resize the rectangle and format it (give it a border, color, shade if you want).

After you have done the above steps, you will have a rectangle shape in the worksheet, and now we will assign a macro to this shape.

Now let’s see how to assign a macro to this shape.

• Right-click on the shape on which you want to assign the macro
• In the Assign Macro dialog box, you will see a list of all the macros that you have in the workbook
• Click on OK

That’s it!

The selected macro has now been assigned to the shape.

Now when you hover the cursor over the shape, it will show the hand icon. which indicates that now this shape has become clickable.

And now if you click on the shape, it will run the assigned macro .

You can type any text within the shape to make it more intuitive (such as ‘Click here to run the macro’). To do this. right-click on the shape and then click on Edit Text. Now you can type within the text box shape.

Note that you won’t be able to click and run the macro when the shape has been selected (i.e., you see a border around the shape that appears when you select it), To make it clickable, hit the Escape key or click anywhere in the worksheet.

Also, when you have assigned the macro to the shape already, you will not be able to select it by using the left mouse key (as it has become clickable and left-click would now execute the macro). In that case, select the shape, hold the control key and then press the left key.

Keeping Shape Visible When you Hide/Resize Rows/Columns

In Excel. when you insert a shape, it sits over the cells – like a chart/object.

This also has a drawback that when you resize or hide rows/columns that have the shape over it, the shape also follows suit.

In the below example, the shape gets hidden when I hide the column on which it’s placed.

If you don’t want this to happen, follow the below steps:

• Right-click on the shape
• In the Format Shape pane (or dialog box in case you’re using Excel 2010 or prior versions), select Size and Properties
• Close the pane (or dialog box)

Now, when you resize rows/columns or hide these, the shape would stay in its place.

Assign a Macro to Form Control Button

If you’re not too concerned with the formatting of the button and are ok with regular gray buttons, you can quickly insert it from form control (or ActiveX control as shown next) and then assign a macro to it.

For this to work, you will need to have the Developer tab in your ribbon. If you don’t have it, here is a detailed step-by-step tutorial on getting the developer tab in the Excel ribbon .

Once you have the developer tab visible, you can use the below steps to quickly insert a button and assign a macro to it:

• Click anywhere on the worksheet. This will insert the button wherever you click and automatically open the ‘Assign Macro’ dialog box.

The above steps would insert a button that has the specified macro assigned to it.

By default, it would be a small button with text such as ‘Button’ written on it. You can change the text to whatever you want and can also change the shape of the button (by dragging the edges).

Since this is an object that is placed over the worksheet (just like shapes/charts), you can drag and place it anywhere in the worksheet.

One drawback of using the Form Control button is that you don’t have much control over the formatting. For example, you can not change the color from gray to something else.

Although there is a little bit of formatting that you can do with a Form control button, it’s nowhere close to what you can do with shapes.

You get these button formatting options when you right-click on the button and then click on Format Control.

This will open the Format Control dialog box where you can change the font type/color, size, alignment, etc.

One good thing about this button is that it doesn’t hide or resize when you hide the rows/columns or resize them. It would, however, move in case you change the height or width or the row/column over which the button is placed.

In case you don’t want the button to stay in its place, you can change the setting by following the below steps:

• Right-click on the button
• Click on Format Control
• Click on the Properties tab

Assign a Macro to an ActiveX Control Button

Apart from the Form Control button, there is also an ActiveX control button to which you can assign a macro.

In most cases, you won’t need to use the ActiveX control button, and I recommend you use it only when you completely understand what it is and you know what you’re doing.

This also, sometimes, make ActiveX a bit glitchy and unpredictable. So, while I cover it in this tutorial, I don’t recommend using ActiveX button and assign a macro to it.

To insert an ActiveX button and then assign a macro to it, follow the below steps:

• Click on the Developer tab
• In the Control group, click on Insert.
• Click anywhere on the worksheet. This will insert the button wherever you click.
• Double-click on the button and it will open the VB Editor backend where you can place the code for the ActiveX button

With ActiveX control, you get a lot more flexibility with a single button. For example, you can specify one macro to be run when you simply click on the button once and another macro when you double-click or even another one when you use the up/down arrow key.

Again, not something you need to be using in your regular work.

Hope you found this tutorial useful. If you’re interested in learning VBA, you can check out more in-depth Excel VBA tutorials here .

You may also like the following Excel tutorials:

• How to Record a Macro in Excel
• Creating a User Defined Function (UDF) in Excel VBA
• Excel VBA MsgBox [Message Box]
• Useful Excel Macro Examples for VBA Beginners
• How to Remove Macros From an Excel Workbook
• How to Enable Macros in Excel?

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

1 thought on “how to assign a macro to a button in excel (easy guide)”.

Following the procedure here, I cannot assign a Macros from an *.xlam workbook (addin). Is there a way to do this? thx

Leave a Comment Cancel reply

BEST EXCEL TUTORIALS

Best Excel Shortcuts

Conditional Formatting

Excel Skills

Creating a Pivot Table

Excel Tables

INDEX- MATCH Combo

Creating a Drop Down List

Recording a Macro

© TrumpExcel.com – Free Online Excel Training

Privacy Policy  | Sitemap

Save Up to 85% LIMITED TIME OFFER

Excel VBA UserForm CommandButton

Table Of Contents

Vba code library, vba reference, vba projects, full access with source code.

Designed and Developed by PNRao

Full Access with VBA Source Code

Well Commented Codes Lines

Creative and Professional Design

Effortlessly Manage Your Projects

120+ project management templates.

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

120+ PM Templates Includes:

50+ Excel Templates

50+ PowerPoint Templates

25+ Word Templates

In this topic:

• VBA CommandButton Control on the UserForm
• Add Dynamic CommandButton Control on the UserForm Using VBA
• Delete CommandButton control on the UserForm using VBA

VBA ActiveX CommandButton Control on the UserForm

Please find more details about VBA ActiveX Command Button Control on the UserForm.

• Go To Developer Tab and then click Visual Basic from the Code or Press Alt+F11.
• Go To Insert Menu, Click UserForm. Please find the screenshot for the same.
• Drag a CommandButton on the Userform from the Toolbox. Please find the below screenshot for the same.
• Now double click on the Command Button, which is dragged on the UserForm .
• Now you can see the following code in the VBA Editor window.
• Now add the following code to the in between above procedure.
• Now, Press ‘F5’ to run the code. You can see the following Output. It is shown in the following Screen Shot.

Add dynamic CommandButton Control on the UserForm using VBA

Please find the following steps and example code, it will show you how to add dynamic Command Button control on the userform.

• Add command button on the userform from the toolbox.
• Right click on the command button, click properties
• Change the command button caption to ‘Create_CommandButton’
• Double click on the command button
• Now, it shows the following code.
• Call the below procedure named ‘Add_Dynamic_CommandButton ’ and find the below procedure to run.

Procedure to call in the Command Button :

• Now, click F5 to run the macro, click ‘Create_CommandButton’ button to see the result.
• You can see the created dynamic Command Button which is shown in the following screen shot.

Delete CommandButton Control on the UserForm using VBA

Please find the below code, it will show you how to delete or remove a command button on the UserForm. In the below example, its deleting the command button named ‘New Button’ which is on the UserForm named ‘UserForm4’. We can use Remove method to delete the controls which are created during run time. Controls which are created during design time cannot be deleted using this method. Please find the below example and screen shots for better understand. Code 1: Adding CommandButton During Run Time

Please find the below screen shot for your reference for the above macro and its output. When we click on Add Command Button:

Code 2: Deleting or Removing CommandButton which is created during run time.

Please find the below screen shot for your reference for the above macro and its output. When we click on Delete Command Button:

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

All-in-One Pack 120+ Project Management Templates

Essential pack 50+ pm templates, excel pack 50+ excel pm templates, powerpoint pack 50+ excel pm templates, ms word pack 25+ word pm templates, ultimate project management template, ultimate resource management template, project portfolio management templates.

Comments are closed.

Effectively Manage Your Projects and  Resources

ANALYSISTABS.COM provides free and premium project management tools, templates and dashboards for effectively managing the projects and analyzing the data.

We’re a crew of professionals expertise in Excel VBA, Business Analysis, Project Management. We’re Sharing our map to Project success with innovative tools, templates, tutorials and tips.

Project Management

Download Free Excel 2007, 2010, 2013 Add-in for Creating Innovative Dashboards, Tools for Data Mining, Analysis, Visualization. Learn VBA for MS Excel, Word, PowerPoint, Access, Outlook to develop applications for retail, insurance, banking, finance, telecom, healthcare domains.

© 2023 Analysistabs | Sitemap | Your Privacy | Terms

#1 Excel tutorial on the net

Add the Controls | Show the Userform | Assign the Macros | Test the Userform

This chapter teaches you how to create an Excel VBA Userform . The Userform we are going to create looks as follows:

Add the Controls

To add the controls to the Userform, execute the following steps.

1. Open the Visual Basic Editor . If the Project Explorer is not visible, click View, Project Explorer.

2. Click Insert, Userform. If the Toolbox does not appear automatically, click View, Toolbox. Your screen should be set up as below.

3. Add the controls listed in the table below. Once this has been completed, the result should be consistent with the picture of the Userform shown earlier. For example, create a text box control by clicking on TextBox from the Toolbox. Next, you can drag a text box on the Userform. When you arrive at the Car frame, remember to draw this frame first before you place the two option buttons in it.

4. Change the names and captions of the controls according to the table below. Names are used in the Excel VBA code. Captions are those that appear on your screen. It is good practice to change the names of controls. This will make your code easier to read. To change the names and captions of the controls, click View, Properties Window and click on each control.

Note: a combo box is a drop-down list from where a user can select an item or fill in his/her own choice. Only one of the option buttons can be selected.

Show the Userform

To show the Userform, place a command button on your worksheet and add the following code line:

We are now going to create the Sub UserForm_Initialize. When you use the Show method for the Userform, this sub will automatically be executed.

1. Open the Visual Basic Editor .

2. In the Project Explorer, right click on DinnerPlannerUserForm and then click View Code.

3. Choose Userform from the left drop-down list. Choose Initialize from the right drop-down list.

4. Add the following code lines:

Explanation: text boxes are emptied, list boxes and combo boxes are filled, check boxes are unchecked, etc.

Assign the Macros

We have now created the first part of the Userform. Although it looks neat already, nothing will happen yet when we click the command buttons on the Userform.

2. In the Project Explorer, double click on DinnerPlannerUserForm.

3. Double click on the Money spin button.

4. Add the following code line:

Explanation: this code line updates the text box when you use the spin button.

5. Double click on the OK button.

6. Add the following code lines:

Explanation: first, we activate Sheet1. Next, we determine emptyRow. The variable emptyRow is the first empty row and increases every time a record is added. Finally, we transfer the information from the Userform to the specific columns of emptyRow.

7. Double click on the Clear button.

8. Add the following code line:

Explanation: this code line calls the Sub UserForm_Initialize when you click on the Clear button.

9. Double click on the Cancel Button.

10. Add the following code line:

Explanation: this code line closes the Userform when you click on the Cancel button.

Test the Userform

Exit the Visual Basic Editor, enter the labels shown below into row 1 and test the Userform.

Learn more, it's easy

• Userform and Ranges
• Currency Converter
• Progress Indicator
• Multiple List Box Selections
• Multicolumn Combo Box
• Dependent Combo Boxes
• Loop through Controls
• Controls Collection
• Userform with Multiple Pages
• Interactive Userform

Download Excel File

• userform.xlsm

Next Chapter

Follow excel easy.

Become an Excel Pro

• 300 Examples

Most Popular

• Range Object
• If Then Statement
• Function and Sub

Userform • © 2010-2023 Popular Excel Topics: Pivot Tables • Vlookup • Formulas • Charts • Conditional Formatting

• Excel Courses
• Free Excel Courses
• Excel Video Tutorials
• Excel Keyboard Shortcuts

Premium Excel Course Now Available!

Build professional - unbreakable - forms in excel, 45 tutorials - 5+ hours - downloadable excel files, use macros with userforms, black friday sale (65%-80% off).

Excel Courses Online Video Lessons Excel Guides

VBA Course (80% Off)

Forms Course (65% Off)

Email Course (65% Off)

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.

Use a Macro in a Module from a UserForm

Do something with a userform from a macro.

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

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:

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

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:

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.

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.

Question? Ask it in our Excel Forum

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

VBA Course - Beginner to Expert

Subscribe for weekly tutorials, bonus: subscribe now to download our top tutorials ebook.

The link to our top 15 tutorials has been sent to you, check your email to download it!

(If you don't see the email, check your Spam or Promotions folder and make sure to add us as a contact so you get our emails in the future.)

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

Assign a macro to a button

You can use a button (a form control) to run a macro that performs an action when a user clicks it. For example, you might use a button to automate the printing of a worksheet, the filtering of data, or the calculation of numbers.

After you create a macro , you can assign it to a button you click to run the macro . You can assign a macro to a button on the Quick Access Toolbar or to a button in your own personal group on the ribbon.

If you want a macro button to be available in other workbooks, assign it to a macro that was created in a personal workbook .

Add a macro button to the Quick Access Toolbar

Click File > Options > Quick Access Toolbar .

In the Choose commands from list, click Macros .

Select the macro you want to assign a button to.

Click Add to move the macro to the list of buttons on the Quick Access Toolbar.

To replace the default macro icon with a different button for your macro, click Modify .

Under Symbol , select a button icon for your macro.

To use a friendlier name for the button, in the Display name box, enter the name you want.

You can enter a space in the button name.

Click OK twice.

The new button appears on the Quick Access Toolbar, where you can click it to run the macro.

Tip:  When you save the workbook , buttons you assign to macros in the personal workbook will be available in every workbook you open.

Add a macro button to your own group on the ribbon

Click File > Options > Customize Ribbon .

Under Customize the Ribbon , in the Main Tabs list , check the Developer box if it is not already checked.

Pick the tab where you want to add your own group.

For example, pick Home , to add your group to the Home tab.

Select New Group .

That adds New Group (Custom) to the tab you picked.

To use a better name for your new group, click Rename , type the name you want in the Display name box, and then click OK .

You can enter a space in the name. For example, type My Macros .

To add a macro to the group, in the Choose commands from list, click Macros .

Select the macro you want to add to your new group, and then click Add . The macro is added to the My Macros group.

To use a friendlier name, click Rename , and then type the name you want in the Display name box.

You can enter a space in the name.

Your new group appears on the tab you picked, where you can click the button to run the macro.

Newer versions

On the Developer tab, in the Controls group, click Button .

If the Developer tab is not available

Go to Excel > Preferences... > Ribbon & Toolbar .

In the Customize the Ribbon section, under Main Tabs , check the Developer check box, and press OK.

Click the worksheet location where you want the upper-left corner of the button to appear.

In the Assign Macro dialog box, click the name of the macro that you want to assign to the button, and then click OK .

To resize the button, drag the sizing handles.

To specify the control properties of the button, Control+Click or right-click the button, and then click Format Control .

Excel 2011 for Mac

On the Developer tab, in the Forms Control group, click Button .

Under Customize , select the Developer check box.

Need more help?

Want more options.

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Microsoft 365 subscription benefits

Microsoft 365 training

Microsoft security

Accessibility center

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Ask the Microsoft Community

Microsoft Tech Community

Windows Insiders

Microsoft 365 Insiders

Was this information helpful?

Thank you for your feedback.

Assign macro with arguments to a Form Control button

One of the most popular methods of running a macro is having a button on the face of the worksheet.  These are simple enough to create with the basic Form Controls found on the Developer Ribbon, which is what makes them a popular option.

But what if you had lots of buttons, all of which did a similar thing apart from a few different variables, arguments or parameters?  One option is writing separate code for each button, or a second option maybe a big If statement to handle the logic for all the buttons.  But please don’t do either of those, that would be crazy.

To pass an argument to a macro just requires the right syntax when assigning the macro to the button.

The Example

For the purposes of proving how this works, here is our example scenario.  There are two Listboxes, each containing a list, with a button below each.   Whenever the button is clicked, the count of items in the Listbox above it is displayed in a message box.

Lets consider how we can achieve this in the most efficient way possible.

Setting up the VBA code

I will assume you already know how to create a button and assign a macro to it.

If we had created separate code for each button, then the VBA code for clicking the button below the lstBox1 would be as follows:

If you notice, the sheet name and Listbox name are hardcoded into the macro, therefore we would need one macro for each button.  Now imagine we had 30 Listboxes and 30 buttons… that would require 30 macros!  Bad idea.

This is where arguments are useful.  We can pass the worksheet name and Listbox name into the macro as arguments, by doing this, we can use a single piece of VBA code.

The code above can be used with any ListBox.  There are no hardcoded variables within the code; they are passed to the code when it is called.

Running a macro with arguments

Having created a macro with arguments in the previous section, it raises a few new issues when assigning it to a button.

• The macro does not appear in the list of available macros.  We can still use the macro, but we have to know it’s name.
• We need to know the right syntax to pass the arguments to the macro

We can handle both of these issue; no big deal.

The syntax required to a call a macro from the same workbook is:

Take careful note of where the single quotes, double quotes, commas and spaces are.  There is nothing to help us complete this, apart from an error message to taunt us when we’ve got it wrong.

To call our lstBoxCount macro from above, the text in the Assign Macro window would be:

Where  Sheet1  is the name of the worksheet and  ListBox1 is the name of the first ListBox.

The same macro could be called from the second button, but the arguments would be different. Notice below the Listbox name has changed.

We can now use the same VBA code no matter how many Listboxes there are, or which worksheets they are on.  We just change the values in the arguments.

Passing Numbers as arguments

If passing a number as an argument do not surround it in double quotes.

Assigning a macro from another workbook

When returning to the Assign Macro window, you will notice Excel has added the name of the workbook into the Macro name box.

At first, this may seem annoying.  But Excel is helping us here, as this is now displaying the syntax required to call a macro from another workbook.

Running a macro with arguments based on a cell value

So far we have assumed we know the arguments when creating the buttons.  That might not be the case; maybe the argument is based on a cell value.  But that is OK too; we can dynamically pass a cell value into the macro at the point the button is clicked by assigning a macro using the following syntax.

The example above assume the argument is contained in Cell A1.  Once again, take careful note of where the single and double quotes are.

Using our specific example.  Let’s assume the name of the Listbox is contained in Cell B2:

This will work too!  Cool, eh?

Wrapping it all up

Hopefully, you see this is very powerful and being able to set arguments based on a cell value is simply amazing.  You no longer need lots of buttons.  Instead, a drop-down box and one button might be sufficient.

Discover how you can automate your work with our Excel courses and tools.

Excel Academy The complete program for saving time by automating Excel.

Excel Automation Secrets Discover the 7-step framework for automating Excel.

Office Scripts: Automate Excel Everywhere Start using Office Scripts and Power Automate to automate Excel in new ways.

5 thoughts on “Assign macro with arguments to a Form Control button”

Go(o)d trick! Trick is compatible: *.xlsm / *.xls (until 2003) – no compatible: *.xlsb (variors error) – if convert: *.xls > *.xlsm … then reassign all “macro” parameters, because rename at convert action.

Thanks, that is useful to know.

This was extremely helpful to solve a problem, thank you. FWIW, Boolean arguments are like numbers and should also not be wrapped in quotes.

Generally: ‘MacroName Boolean’

My specific example: ‘ClearData True’

Then upon saving, Excel 365 added the workbook name: ‘yourworkbookname.xlsm’!’ClearData True’

What I don’t understand is why it is adding the workbook name, because it IS in the same workbook. Either way, it works, so thanks!

To automatic Excel function: full path, see above text: “Assign macro with arguments to a Form Control button”.

Curious if anyone else is seeing this. Used the above to pass a text string to a procedure via a form control button and it works fine. However after I save, close, reopen the area where I assigned the call with the argument has been prepended with the workbook name and clicking the button now fails. Behavior seems the same regardless of what the “Macros In” drop down is set to.

Leave a Comment Cancel reply

VBA UserForm – A Guide for Everyone

by Paul Kelly | Apr 22, 2016 | UserForms | 68 comments

This post provides an in-depth guide to the VBA Userform starting from scratch.

The table of contents below shows the areas of the VBA UserForm that are covered and the section after this provides a quick guide so you can refer back to the UserForm code easily.

“The system should treat all user input as sacred.” – Jef Raskin

• 1 A Quick Guide to the VBA UserForm
• 2 The Webinar
• 3 Introduction
• 4 Related Articles
• 5 Download the Code
• 6 What is the VBA Userform?
• 7.1 VBA MsgBox
• 7.2 InputBox
• 7.3 GetOpenFilename
• 8 How to Create a VBA UserForm
• 9 Designing the VBA UserForm
• 10 A Very Simple VBA UserForm Example
• 11 Setting the Properties of the UserForm
• 12 The Controls of the VBA UserForm
• 13 Adding Code to the VBA UserForm
• 14 Adding Events to the VBA UserForm
• 15.1 Initialize versus Activate
• 16.1 Modal Userform
• 16.2 Modeless Userform
• 16.3 Modal versus Modeless
• 16.4 Typical use of a Modal form
• 16.5 Typical use of a Modeless form
• 17.1 Using UserForm_QueryClose to Cancel the UserForm
• 17.2 Using the Escape key to cancel
• 17.3 Putting All the Modal Code Together
• 18 VBA Minimize UserForm Error
• 19 Part 2 of this post
• 20 What’s Next?

A Quick Guide to the VBA UserForm

The following table provides a quick guide to the most common features of the UserForm

The Webinar

If you are a member of the website , click on the image below to view the webinar for this post.

( Note: Website members have access to the full webinar archive .)

Introduction

The VBA UserForm is a very useful tool. It provides a practical way for your application to get information from the user.

If you are new to UserForms you may be overwhelmed by the amount of information about them. As with most topics in VBA, 90% of the time you will only need 10% of the functionality.

In these two blog posts( part 2 is here ) I will show you how to quickly and easily add a UserForm to your application.

This first post covers creating the VBA Userform and using it as modal or modeless . I will also show you how to easily pass the users selection back to the calling procedure.

In the second part of this post I will cover the main controls such as the ListBox, the ComboBox(also called the Dropdown menu), the TextBox and the CheckBox. This post will contain a ton of examples showing how to use each of these controls.

Related Articles

VBA Message Box VBA UserForm Controls

Download the Code

What is the vba userform.

The VBA UserForm is a dialog which allows your application to get input from the user. UserForms are used throughout all Windows applications. Excel itself has a large number of UserForms such as the Format Cells UserForm shown in the screenshot below.

Excel’s “Format cells” UserForm

UserForms contain different types of controls such as Buttons, ListBoxes, ComboBoxes(Dropdown lists), CheckBoxes and TextBoxes.

In the Format Cells screenshot above you can see examples of these controls:

• Font, Font style and Size contain a textbox with a ListBox below it
• Underline and Color use a Combobox
• Effects uses three CheckBoxes
• Ok and Cancel are command Buttons

There are other controls but these are the ones you will use most of the time.

The Built-in VBA Userforms

It is important to note that VBA has some useful built-in UserForms. These can be very useful and may save you having to create a custom one. Let’s start by having a look at the MsgBox.

The VBA message box allows you to display a dialog to the user. You can choose from a collection of buttons such as Yes, No, Ok and Cancel.

You can easily find out which of these buttons the user clicked on and use the results in your code.

The following code shows two simple examples of using a message box

In the next example, we ask the user to click Yes or No and print a message displaying which button was clicked

In the final example we ask the user to click Yes, No or Cancel

You can see all the MsgBox options here .

If you want to get a single piece of text or value from the user you can use the InputBox . The following code asks the user for a name and writes it to the Immediate Window(Ctrl + G):

You can add validation to the InputBox function using the Type parameter:

You can download the workbook with all the code examples from the top of this post .  

GetOpenFilename

We can use the Windows file dialog to allow the user to select a file or multiple files.

The first example allows the user to select a file

The following example allows the user to select multiple files

Note: If you need more flexibility then you can use the File Dialog . This allows you to use the “Save as” file dialog, select folders and so on.

How to Create a VBA UserForm

If the built-in UserForms do not cover your needs then you will need to create your own custom Userform. To use a UserForm in our code we must first create one. We then add the necessary controls to this Userform.

We create a UserForm with the following steps

• Open the Visual Basic Editor(Alt + F11 from Excel)
• Go to the Project Window which is normally on the left(select View->Project Explorer if it’s not visible)
• Right-click on the workbook you wish to use
• Select Insert and then UserForm (see screenshot below)

Creating a Userform

A newly created UserForm will appear. Anytime you want to access this Userform you can double click on the UserForm name in the Project window.

The Toolbox dialog should also be visible. If it’s not visible select View->Toolbox from the menu. We use the toolbox too add controls to our UserForm.

The UserForm Toolbox

Designing the VBA UserForm

To view the design of the UserForm, double click on it in the Project window. There are three important windows we use when creating our UserForms.

• The UserForm
• The properties window – this is where we can change the setting of the Userform and its controls
• The toolbox – we use this to add new controls to our UserForm

UserForm Windows

A Very Simple VBA UserForm Example

Let’s have a look at a very simple UserForm example.

You can download this and all the code examples from the top of this post .

• Create a new UserForm
• Rename it to userformTest in the (Name) property in the properties window
• Create a new module(Right-click on properties window and select Insert->Module)
• Copy the DislayUserForm sub below to the module
• Run the sub using Run->Run UserForm Sub from the menu
• The UserForm will be displayed – you have created your first UserForm application!
• Click on the X in the top right of the UserForm to close

Setting the Properties of the UserForm

We can change the attributes of the UserForm using the properties window. Select View->Properties Window if the window is not visible.

When we click on the UserForm or a control on a UserForm then the Properties window displays the attributes of that item.

VBA Properties Window

Generally speaking, you only use a few of these properties. The important ones for the UserForm are Name and Caption.

To change the name of the UserForm do the following

• Click on the UserForm in the Project window or click on the UserForm itself
• Click in the name field of the properties window
• Type in the new name

The Controls of the VBA UserForm

We add controls to the UserForms to allow the user to make selections, enter text or click a button. To add a control use the steps below

• Go to the toolbox dialog – if not visible select View->Toolbox
• Click on the control you want to add – the button for this control will appear flat
• Put the cursor over the UserForm
• Hold down the left mouse button and drag until the size you want

The following table shows a list of the common controls

Adding Code to the VBA UserForm

To view the code of the UserForm

• Right-click on the UserForm in the properties windows(or the UserForm itself) and select “View Code”
• You will see a sub called UserForm_Click . You can delete this when you create your first sub

Note: If you double click on a control it will bring you to the click event of that control. This can be a quicker way to get to the UserForm code.

Adding Events to the VBA UserForm

When we use a UserForm we are dealing with events. What this means is that we want to perform actions when events occur. An event occurs when the users clicks a button, changes text, selects an item in a ComboBox, etc. We add a Sub for a particular event and place our code in it. When the event occurs our code will run.

One common event is the Initialize event which occurs when the UserForm is created at run time. We normally use this event to fill our controls with any necessary data. We will look at this event in the section below.

To add an event we use the ComboBoxes over the code window(see screenshot above). The left one is used to select the control and the right one is used to select the event. When we select the event it will automatically add this sub to our UserForm module.

Note: Clicking on any control on the UserForm will create the click event for that control.

The Initialize Event of the VBA UserForm

The first thing we want to do with a UserForm is to fill the controls with values. For example, if we have a list of countries for the user to select from we could use this.

To do this we use the Initialize event . This is a sub that runs when the UserForm is created(see next section for more info).

To create the Initialize event we do the following

• Right-click on the UserForm and select View Code from the menu.
• In the Dropdown list on the left above the main Window, select UserForm .
• This will create the UserForm_Click event. You can ignore this.
• In the Dropdown list on the right above the main Window, select Initialize .
• Optional: Delete the UserForm_Click sub created in step 2.

Adding the Initialize Event

We can also create the Initialize event by copying or typing the following code

Once we have the Initialize event created we can use it to add the starting values to our controls. We will see more about this in the second part of this post.

Initialize versus Activate

The UserForm also has an Activate event . It is important to understand the difference between this and the Initialize event.

The Initialize event occurs when the actual object is created. This means as soon as you use on of the properties or functions of the UserForm. The code example below demonstrates this

We normally reference the UserForm first by calling Show which makes it seem that displaying the UserForm is triggering the Initialize event. This is why there is often confusion over this event.

In the example below calling Show is the first time we use the UserForm. Therefore it is created at this time and the Initialize event is triggered.

The Activate event occurs when the UserForm is displayed. This can happen using Show . It also occurs any time the UserForm is displayed. For example, if we switch to a different window and then switch back to the UserForm then the Activate event will be triggered.

We create the Activate event the same way we create the Initialize event or we can just copy or type the following code

• Initialize occurs when the Userform is created. Activate occurs when the UserForm is displayed.
• For each UserForm you use – Initialize occurs only once, Activate occurs one or more times.

Calling the VBA UserForm

We can use the VBA UserForm in two ways

Let’s look at each of these in turn.

Modal Userform

Modal means the user cannot interact with the parent application while this is visible. The excel Format cells dialog we looked at earlier is a modal UserForm. So are the Excel Colors and Name Manager dialogs.

We use modal when we don’t want the user to interact with any other part of the application until they are finished with the UserForm.

Modeless Userform

Modeless means the user can interact with other parts of the application while they are visible. An example of modeless forms in Excel is the Find dialog(Ctrl + F).

You may notice that any Excel dialog that allows the user to select a range has a limited type of Modeless – the user can select a range of cells but cannot do much else.

Modal versus Modeless

The actual code to make a UserForm modal or modeless is very simple. We determine which type we are using when we show the UserForm as the code below demonstrates

As the comments above indicate, the code behaves differently for Modal and Modeless. For the former, it waits for the UserForm to close and for the latter, it continues on.

Even though we can display any UserForm as modal or modeless we normally use it in one way only. This is because how we use them is different

Typical use of a Modal form

With a Modal UserForm we normally have an Ok and a Cancel button.

The Ok button normally closes the UserForm and performs the main action. This could be saving the user inputs or passing them back to the procedure.

The Cancel button normally closes the UserForm and cancels any action that may have taken place. Any changes the user made on the UserForm are ignored.

Typical use of a Modeless form

With a Modeless UserForm we normally have a close button and an action button e.g. the Find button on the Excel Find Dialog .

When the action button is clicked an action takes place but the dialog remains open.

The Close button is used to close the dialog. It normally doesn’t do anything else.

A VBA UserForm Modal Example

We are going to create a Modal UserForm example. It is very simple so you can see clearly how to use a UserForm.

The following UserForm allows the user to enter the name of a fruit:

We use the following code to show this UserForm and to retrieve the contents of the fruit textbox:

What you will notice is that we hide the UserForm when the user clicks Ok. We don’t set it to Nothing or unload it until after we are finished retrieving the user input. If we Unload the UserForm when the user clicks Ok then it no longers exists so we cannot access the values we want.

Using UserForm_QueryClose to Cancel the UserForm

We always want to give the user the option to cancel the UserForm. Once it is canceled we want to ignore any selections the user made.

Each form comes with an X in the top right-hand corner which allows the user to cancel it:

The X button on the UserForm

This button cancels the UserForm automatically – no code is necessary. When the user clicks X the UserForm is unloaded from memory. That is, it no longer exists so we will get an error if we try to access it. The code below will give an error if the user clicks on the X

To avoid this error we want to prevent the UserForm from being Unloaded when the X button is clicked. To do this we use the QueryClose event.

We create a variable first at the top of the UserForm code module. We also add a property so that we can read the variable when we need to retrieve the value:

Then we add the UserForm_QueryClose event to the UserForm module:

In the first line, we prevent the UserForm from being unloaded. With the next lines, we hide the UserForm and set the m_Cancelled variable to true. We will use this variable later to check if the UserForm was canceled:

We can then update our calling procedure to check if the UserForm was canceled

If we want to add a Cancel button it is simple to do. All we need to do is Hide the form and set the variable m_Cancelled to true. This is the same as we did in the QueryClose Event above:

Using the Escape key to cancel

If you want to allow the user to cancel using the Esc it is simple(but not obvious) to do. You set the Cancel property of your ‘Cancel’ button to True. When Esc is pressed the click event of your Cancel button will be used.

Putting All the Modal Code Together

The final code for a Modal form looks like this:

&nbps; You can use this code as a framework for any Modal UserForm that you create.

VBA Minimize UserForm Error

We are now going to use a simple example to show how to use a Modeless VBA UserForm. In this example, we will add a customer name to a worksheet each time the user clicks on the Add Customer button.

The code below displays the UserForm in Modeless mode. The problem with this code is that if you minimize Excel the UserForm may not be visible when you restore it:

The code below solves the problem above. When you display a Userform using this code it will remain visible when you minimize and restore Excel:

An important thing to keep in mind here is that after the frm.Show line, the code will continue on. This is different to Modal where the code waits at this line for the UserForm to be closed or hidden.

When the Add button is clicked the action occurs immediately. We add the customer name to a new row in our worksheet. We can add as many names as we like. The UserForm will remain visible until we click on the Close button.

The following is the UserForm code for the customer example:

Part 2 of this post

You can find the second part of this post here .

What’s Next?

Free VBA Tutorial If you are new to VBA or you want to sharpen your existing VBA skills then why not try out the The Ultimate VBA Tutorial .

Related Training: Get full access to the Excel VBA training webinars and all the tutorials .

( NOTE: Planning to build or manage a VBA Application? Learn how to build 10 Excel VBA applications from scratch.)

68 Comments

Hi Paul. I have a EXCEL VBA userform question. I have a text box (call in info1) that I want to be able to change if the click on the field. The event procedure _change() only comes when the field is physically changed. Is there an event procedure if the user clicks on the field (note: sub info_click() does not work)

Try the info1_enter proc, which will trigger whenever the field is entered, either by clicking or tabbing.

Thanks for this article – very helpful indeed! I am wondering how I ‘blank out’ the white space of my user form so that only the ‘Submit’ or ‘Cancel’ buttons can actually activate an action. At the moment, if I complete the form with example data and then click on any part of the form other than the ‘OK’ button, the form still closes… I hope this is clear and look forward to hearing back from you.

Many thanks, Michael

How do i display a pdf file in a userform . I have windows 7 [64-bit] Excel 2016 [64bit] and Acrobat 9 DC .

Hi Paul, I need help with printing to a label printer from vba

In section Initialize versus Activate of this paper you state that UserForm_Initialize is triggered when form.show is called. You also state that userForm_Initialize is called when the form is crreated. That are two differenmt statements. I Think that UserForm_Initialize is executed in Load Form. UserForm_Activate is called in Form.Show just after form becomes visible. I tested it with msgbox UserForm_Initialize is triggered indeed in Load and my message after load cal appaers after initialize has run. Then my message in the activate event shows up just after the forms is shown in form.show. Probably you mean it this way but the text is not clear and confusing.

“you state that UserForm_Initialize is triggered when form.show is called” – I didn’t say that I said this:

“We normally reference the UserForm first by calling Show which makes it seem that displaying the UserForm is triggering the Initialize event. This is why there is often confusion over this event. In the example below calling Show is the first time we use the UserForm. Therefore it is created at this time and the Initialize event is triggered.”

excel vba userform is moving its location every time i click any combobox

I am using Userform1 multiple times in different Excel worksheets. But, If I select Checkbox in the first Userform then another Userforms checkbox automatically selected. How can I separate them?

I’m a beginner in coding so do not have much idea how can I segregate them to perform individually.

How would you keep one userform always on top of another userform and allow editing changes to both without the topmost userform “disappearing ” , that is moving behind the other userform? \ Should there not be a way to keep userform A as topmost, yet allow interacting with another userform B “behind” it ? – say via a Wait statement? Pls help.

If I’ve done extensive editing of the code in the user form code module, does that impact the workbook in any way? If so, what can I do to remedy this?

My “no modal” dialog is always on top. I would like to modify this, so that I can put other active documents on top of it, without it being minimized. How can the behavior of the dialog be changed, so that it supports other windows on top of it? Thanks,

Hi there…thank you for the detailed explanation.I was using the code above using MS office 2010 excell. When I try to run the Macro, the userform doesnot show up and I am getting an error code of 438 for the show function, any idea how toresolve this?

Submit a Comment Cancel reply

Your email address will not be published. Required fields are marked *

You are not logged in

You are not currently logged in.

• Search forums

Follow along with the video below to see how to install our site as a web app on your home screen.

Note: This feature currently requires accessing the site using the built-in Safari browser.

• If you would like to post, please check out the MrExcel Message Board FAQ and register here . If you forgot your password, you can reset your password .
• Question Forums
• Excel Questions

Assigning a macro to a command button on a user form.

• Thread starter t406jaw
• Start date Dec 13, 2002

Board Regular

• Dec 13, 2002

I have three comand buttons on my user form called user form 3, they are called New cars, Sports and Performance and See current Quote. I then have three macro's called see_newcars, see_sportsandperformance and see_currentquote. Is it possible to assign these to the relevant buttons, if so explain. If not and a code has to be entered suggestions welcome.  

Excel Facts

Mrexcel mvp.

You don't so much assign as use the button's click event to call the macro. Double-click on the button in design mode, this will take you into the userform module in the VBA editor and automatically create the click event for you (as this is te default event for that control). Enter a line to call your macro, eg- Private Sub CommandButton1_Click() Call YourMacroName End Sub  

Similar threads

• Sep 12, 2023

• Jun 15, 2023
• Oct 16, 2023

• Aug 13, 2023
• Sep 11, 2023

Forum statistics

Share this page.

We've detected that you are using an adblocker.

Which adblocker are you using.

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock

Power Spreadsheets

Excel and VBA tutorials and training. Learn how to use Microsoft Excel and Visual Basic for Applications now.

Create Excel UserForms For Data Entry In 6 Easy Steps: Tutorial And Practical Example

When working in Excel, you have a few ways to get information from a user and allow them to enter data. These include the following:

• Message boxes.
• Input boxes.
• The GetOpenFilename method .
• The GetSaveAsFilename method .
• The FileDialog method .
• Excel worksheets .

As explained in Mastering VBA for Microsoft Office 2016 :

Dialog boxes and forms are among the most powerful and feature-packed elements of VBA.

In this UserForms Tutorial, you find all the information you need to start creating your own UserForms in Excel . This blog post is organized as follows:

• First section: I introduce (i) UserForms, (ii) the simple 6-step process you can use to create Excel UserForms, and (iii) the main constructs you use when designing and programming UserForms.
• Second section: I walk you through a step-by-step practical example of how to create a simple UserForm for data entry. In this section, you can see how the process of creating a UserForm (described in section #1) looks in practice.

This Tutorial is accompanied by an Excel workbook example that allows you to follow each of the steps I describe in the second section. You can get immediate free access to this workbook by clicking the button below.

The following Table of Contents lists the main sections of this Tutorial.

Table of Contents

What Is A UserForm

When working with VBA, you usually use the term “UserForm” to refer to dialog boxes.

A UserForm is an object. This object represents a window or dialog box within Excel's User Interface. By working with the UserForm object, you can easily create custom dialog boxes . Each of these dialog boxes you create is held within an UserForm object.

You can think of a UserForm as a blank canvas. Within this canvas, you can place different elements, known as controls . Controls are, themselves, also objects. UserForm controls are the objects users work with to provide input through the UserForm.

You can find a description of the main controls you can work with further below. These controls are commonly used within Excel built-in dialog boxes. Therefore, once created, the general appearance and behavior of the custom dialog boxes you create with UserForms is usually like that of Excel's built-in dialogs.

Why Work With UserForms

UserForms are very flexible . Therefore, you can create very complex and sophisticated UserForms that achieve a variety of different purposes and help you in different scenarios.

As I mention at the beginning of this UserForms Tutorial, there are a few ways in which you can display information and get input from a user. Some of the non-UserForm alternatives, such as Message and Input Boxes, work great in relatively simple cases . In fact, in such simple cases, Message and Input Boxes are probably more appropriate than UserForms.

There are, however, several circumstances where those alternatives aren't powerful enough for your purposes . Consider, for example, the following scenarios or considerations:

• A user should make choices or decisions that can't be adequately captured by the limited controls available in Message or Input boxes.
• The macro requires the user to make several different data entries.
• The user input you require is too specialized or complex for Message or Input boxes.
• You want to restrict the choices users can make or the data they can enter by establishing sophisticated or complex data validation and controlling rules.
• You want to ensure that users can easily navigate a data entry form without confusion.

More broadly, and in the words of Excel MVP Tom Urtis (in the Excel VBA 24-Hour Trainer ):

UserForms enable you to interact with your users in ways that you can't when using standard Message Boxes, InputBoxes, or controls embedded onto your worksheet.

This doesn't mean that UserForms and, more generally, ActiveX controls are perfect or that they should be your go-to choice. There are some downsides to UserForms . For example, as explained by Excel MVP Jordan Goldmeier in Advanced Excel Essentials , UserForms can sometimes act unpredictably. Jordan also explains how differences in factors such as “internal settings and hardware” may result in UserForms appearing “different across different computers”.

How To Create An Excel UserForm: Overview

At a basic level, you can create a UserForm in the following 6 simple steps :

• Insert a UserForm.
• Add controls to the UserForm.
• Move or resize the added UserForm controls, as required.
• Customize the UserForm or its controls.
• Assign VBA code to the UserForm.
• Display or load the UserForm.
• Close or hide the UserForm.

How To Insert A New UserForm

You can insert a UserForm in the following 3 easy steps :

• Go to the Visual Basic Editor by, for example, using the “Alt + F11” keyboard shortcut .

As an alternative to steps #2 and #3 above , you can proceed as follows:

Once you complete the process above, Excel the inserts the UserForm . By default, the UserForm contains an empty dialog box. Notice that, in addition to displaying the UserForm window on the right side of the screen , the VBE adds the following items to the Project Explorer :

• A Forms node. If the VBA Project you're working on already contains a Forms node, the VBE doesn't add it again.
• A new UserForm. This UserForm appears within the Forms collection.

A UserForm object can hold a single dialog box. Therefore, you must create and  insert a new UserForm for each dialog box you want to create . However, you have significant flexibility regarding the amount of UserForms you can store within a single workbook .

Usually, in addition to displaying the new UserForm, the VBE shows a floating window known as the Toolbox. The Toolbox is, also, usually displayed when you activate a UserForm.

This Toolbox is the feature you use to add controls to the UserForm you just created. You can learn more about it in the appropriate section below.

If your Visual Basic Editor doesn't display the Toolbox , do either of the following:

How To Add Controls To A UserForm With The Toolbox

You can add a control to a UserForm in 3 simple steps :

• If necessary, you can move or resize the control, using commonly-used methods. You can find further details about some of these methods below.

If you need to add the same control several times to a single UserForm , you can take advantage of a shortcut by proceeding as follows:

• Click on the UserForm, where you want to add the control.

In the example above, I work with a Label. This is only 1 of the different controls you can add to your UserForms. To better understand the different controls you can add, let's explore…

The Toolbox

The Toolbox is a relatively simple floating window that allows you to add ActiveX controls to your UserForms . You work with the Toolbox because the VBE's menu doesn't have commands that allow you to add UserForm controls.

It contains a single tab (Controls) and 16 icons.

Out of the 16 icons that appear in the Toolbox, 15 are controls you can insert in your UserForms . You can divide these controls in the following groups :

• Controls that influence the appearance of your UserForm.
• Controls that allow users to enter data, make selections or issue a command.

The remaining control is Select Objects. As indicated by its name, Select Objects allows you to select and manipulate the different elements of your UserForm.

You rarely need to click on the Select Objects control . This is because Select Objects is the default control and the mouse pointer usually returns to this mode automatically. According to author Richard Mansfield (in Mastering VBA for Microsoft Office 2016 ), there are 2 common cases where you need to click on the Select Objects control:

• If you select a control but change your mind before using it.
• If you add the same control several times to a single UserForm by using the technique I describe in the previous section (double-clicking on the control within the Toolbox).

In both cases, you restore the mouse pointer to Selection mode by clicking on Select Objects.

Which controls you choose to add to a UserForm depend on the objective you're trying to achieve. The following sections briefly introduce the 15 controls that are available in the Toolbox.

Group #1: Controls That Influence The Appearance Of Your UserForm

This group of control allows you to customize your UserForm.

Group #2: Controls That Allow Users To Enter Data, Make Selections Or Issue A Command

This group of controls allow the users of your UserForm to (i) enter data, (ii) make selections, or (iii) issue commands.

You're, strictly speaking, not limited to using the built-in controls within the Toolbox or Excel. This is because you can customize the Toolbox and use other ActiveX controls . However, in this UserForms Tutorial, I introduce only the controls that appear within the Toolbox. Those are the most commonly-used.

How To Select, Move Or Resize UserForm Controls And UserForms

Once you've added a control to a UserForm, you can easily move or resize it. You generally do this with the mouse. The following are some of the most commonly-used methods to move or resize controls or the UserForm itself :

The UserForm Grid

When you're working with a UserForm, the VBE usually displays dots forming a grid.

The purpose of such grid is to help you align all the controls within the UserForm. Depending on your settings, the controls you work with snap automatically to the grid.

The grid is, in any case, not visible in the actual UserForm displayed to the users.

You can specify 3 settings related to this dot grid:

• Whether the grid is visible or not.
• The size of the grid elements.
• Whether controls snap to the grid or not.

You determine these settings from the General tab of the Options dialog . You can get to this tab in the following 2 easy steps:

The settings that apply to the grid are grouped under Form Grid Settings . Within this group of settings, you find the following:

Once you've selected the settings you want to use, click the OK button to confirm your selections.

The Format Menu Or UserForm Toolbar

You can use the commands in the Format menu or the UserForm toolbar to arrange the controls within a UserForm.

I describe the main commands below. First, let's look at the different ways you can access them:

How To Access The Commands In The Format Menu Or UserForm Toolbar

You can access the commands within the Format Menu or UserForm toolbar using any of the following 3 methods:

• Select the control(s) you want to manipulate.

• Select the control(s) you want to work with.

• Select the control(s) and right-click on them.

The available commands may vary slightly, depending on the context you're in and which of the 3 methods above you use to access them.

The order in which you select the controls matters :

When resizing or moving controls, the VBE must use 1 of them as a reference. You can identify which controls are moved or resized, and which control is used as reference, based on the color of their sizing handles. Controls that are resized or moved have black sizing handles . The control that is used as reference has white sizing handles .

The reference control is usually the last one you click on prior to executing the command . This allows you to choose the reference control after you've selected all the controls you want to manipulate.

Commands In The Format Menu Or UserForm Toolbar

The following are the 11 options displayed by the VBE when you expand the Format menu. Several of these commands are also available through the UserForm toolbar or a context menu, as I explain above.

• Lefts: Aligns the selected controls to the left border of the reference control.
• Centers: Aligns the (horizontal) center of the selected controls.
• Rights: Aligns the selected controls to the right border of the reference control.
• Tops: Aligns the selected controls to the top border of the reference control.
• Middles: Aligns the (vertical) center of the selected controls.
• Bottoms: Aligns the selected controls to the bottom border of the reference control.
• To Grid: Snaps the selected control(s) to the grid.

• Width: Resizes the selected controls to be of the same width as the reference control. The height of the controls doesn't change.
• Height: Resizes the selected controls to be of the same height as the reference control. The width of the controls isn't changed.
• Both: Resizes the selected controls to have the same height and same width.

• Make Equal: Makes the horizontal spaces between the selected controls equal.
• Increase: Increases the horizontal spacing between the controls.
• Decrease: Decreases the horizontal spacing between the controls.
• Remove: Removes the horizontal spacing between the controls.

• Make Equal: Makes the vertical spaces between the selected controls equal.
• Increase: Increases the vertical spacing between the controls.
• Decrease: Decreases the vertical spacing between the controls.
• Remove: Removes the vertical spacing between the controls.

• Horizontally.
• Vertically.

• Bottom: Arranges the selected CommandButton(s) on the bottom of the UserForm.
• Right: Arranges the selected CommandButton(s) on the right side of the UserForm.

• Bring to Front: Brings the selected control to the front of the stack.
• Send to Back: Sends the selected control to the back of the stack.
• Bring Forward: Brings the selected control 1 step forward.
• Send Backward: Sends the selected control 1 step backward.

The UserForm toolbar contains several of the commands I list above. Additionally, it contains a Zoom drop-down list . This zoom feature allows you to zoom into or out of the UserForm controls. In other words, controls are resized per the percentage you choose.

How To Customize A UserForm Or Its Controls

You customize a UserForm or its controls (all of which are objects) by modifying their properties . You can specify these properties in 3 ways :

• Programmatically, using VBA code. You usually rely on this option if you want to set properties at runtime. This means that you can use VBA to set properties when the UserForm is displayed.
• Manually, by manipulating the object within the UserForm window. This applies only to some properties, particularly those related to size and position such as Height, Width, Left and Top. You can learn more about this topic by reading the previous section.
• Manually, using the Properties Window of the VBE. This is the option you generally use while designing your UserForm within the VBE and is the focus of this section. The properties you set through the Properties Window are usually static (vs. dynamic) and, therefore, you rarely use VBA code (#1 above) to modify them later.

While designing a UserForm within the VBE, you can change a property through the Properties Window in the following 3 easy steps:

In this UserForms Tutorial, I focus on the basics of creating and designing UserForms. Therefore, I only explain how to use the Properties Window of the VBE for these purposes. In the UserForm example that appears in the second section of this blog post, you can find some basic examples of how to use VBA to set the properties of a UserForm or its controls programmatically.

UserForm Or Control Properties Vs. Other VBA Properties

Objects within the Excel VBA Object Model  have properties. Properties are the attributes, characteristics or qualities that you can use to describe an object.

The UserForm object and the controls within the UserForm itself are also objects and, therefore, also have properties . The basic purpose of these properties doesn't change: they allow you to describe the attributes, characteristics or qualities of an object.

There's, however, an important difference between the way you usually work with properties (i) within VBA, and (ii) when dealing with UserForms and UserForm controls.

When you create macros , you usually use VBA code to either return (read) or modify (write) the current property setting . When you work with UserForms, you can also use VBA. However, you can also modify UserForm and UserForm control properties in a different way:

By using…

The Properties Window

The Properties Window is commonly displayed on the lower left corner of the VBE, although this varies. You can get the VBE to show the Properties Window by, for example, using the “F4” keyboard shortcut .

The Properties Window shows the properties of the currently-selected object . If your current selection includes more than 1 object, the Properties Window lists the properties that are common to the selected objects.

Alternatively, you can use the drop-down list at the top of the Properties Window to specify the object whose properties you want to see.

Objects have different properties, although some properties are commonly-shared by different objects. Therefore, the appearance of the Properties Window changes depending on the object you select.

Additionally, the Properties Window can display the properties of the same object organized per 2 different criteria. You select the organization criteria by choosing 1 of the 2 tabs the Properties Window has:

Regardless of the categorization criteria you use, the properties don't change. The Properties Window lists an object's properties and those properties don't change just because they're listed in a different order.

How To Modify Properties With The Properties Window

The Properties Window usually contains 2 columns. The left column lists the name of the properties. The right column shows the current property setting.

Therefore, once you've selected the object you want to work with , you can proceed as follows:

You can modify the properties of several objects at the same time . To do this, select all the objects whose property you want to modify prior to executing the 2-step process I describe above. When you select several objects at the same time, the Properties Window displays those properties that are common to the selection.

Some of the properties of the UserForm are default settings for new controls . In other words, the new controls you add to the UserForm are influenced by that property setting. Therefore, if you modify those default control settings:

• Controls added after the property modification use the new property setting, but…
• Controls added prior to the property modification remain unchanged and don't use the new property setting.

Why Modify The Name Property Of Your UserForms Or UserForm Controls

Each object has its own properties. These properties generally vary from object to object. There are, however, some properties that are common to several different objects . Commonly-used properties include Name, Width, Height, Left, Right, Value and Caption. In fact, UserForms and all UserForm controls have the Name property.

In practice, you usually modify just a few properties through the Properties Window . Name is 1 such property that you usually set through the Properties Window.

Every time you add a UserForm to a Project, the Visual Basic Editor assigns a default name :

“#” is an integer that starts at 1 and increases sequentially. Therefore, the first UserForm you add is UserForm1. The second is UserForm2. The third is UserForm3. And so on…

This may look familiar. Excel uses similar naming conventions for workbooks, worksheets, and regular VBA modules (among others).

That same rule applies to the controls within a UserForm. For example, if you're working with Label controls, the first Label is named Label1. The second is Label 2. And so on…

You can find the name of a UserForm or UserForm control by checking its Name property in the Properties Window. This property is important:

You use the Name property of a UserForm or a UserForm control to refer to it when creating your VBA code . Since you may use these names relatively often, you may prefer using names that are more descriptive and meaningful than the default ones.

Notice, additionally, that any time you change the Name property of a UserForm or a UserForm control, you may have to go back to your VBA code to update the object references. To avoid this, you may prefer modifying the names of your UserForms and UserForm controls as soon as possible after creating them and prior to writing your VBA code .

How To Name UserForms Or UserForm Controls

Several of the general naming rules, such as those that apply to properties and Sub procedures, are also applicable to UserForms and UserForm controls. The following are the main rules you may want to consider when setting the names of your UserForm and UserForm controls:

• The maximum name length is 40 characters.
• The first character must be a letter.
• Characters, other than the first, can include letters, numbers and underscores (_).
• Names can't include spaces ( ), periods (.), mathematical operators (such as +, -, /, * or ^), comparison operators (for example, >, < or =), or certain punctuation characters (such as @, #, $, %, &, and !).
• The scope of a UserForm is, generally, the whole VBA Project. Therefore, UserForm names must be unique within the workbook.
• The scope of a UserForm control is, generally, the UserForm where its located. Therefore, UserForm control names must be unique within the UserForm.

A common naming convention for UserForms and UserForm controls involves adding a control identifier at the beginning of the name . You do this by adding a 3-letter prefix to the control name. The following table lists some commonly used prefixes described in, among others, Excel 2016 Power Programming with VBA and Mastering VBA for Microsoft Office 2016 .

How To Remove Controls From A UserForm

You can easily delete controls from a UserForm using either of the following 2 methods:

How To Assign VBA Code To A UserForm

Once you complete the design of your UserForm, you must create the macros that power it and respond to the user's actions. You can create and assign macros to a UserForm in the following 2 easy steps:

• Go to the Code window of the UserForm by, for example, using the “F7” keyboard shortcut.
• Enter the appropriate VBA code within this Code window.

This section focuses on the macros that you attach or assign to the UserForm. This is different from the macros responsible for displaying the UserForm in the first place.

In other words, when working with UserForms, you deal with (at least) 2 different types of Sub procedures:

• A Sub procedure that displays the UserForm. You can read more about this topic in a section further below.
• 1 or more event-handler procedures, which are attached to the UserForm. This is the topic of this section.

Additional considerations apply to the VBA code you use to close the UserForm, even though this code is commonly attached to the UserForm. I explain how you can close or hide a UserForm further below.

What Is A UserForm Code Window And Why You Work With It

When working with UserForms, you create event-handler procedures that are stored within the code module of the appropriate UserForm object. In other words:

• UserForms are class objects have a code module. This is like what occurs with other object classes, such as Worksheets or Workbooks.
• You use this module to store the procedures to be executed when a user works with the UserForm.
• The procedures you assign to UserForms are event-handler procedures. In other words, these procedures are executed when an event occurs. In the case of UserForms, these events are the consequence of the user interacting with the UserForm.

Notice the difference between the following:

• The event-handler procedures which are executed when a user works with the UserForm. These event-handler procedures are stored within the UserForm Code window.
• The procedure(s) that control the display of the UserForm. These procedures are usually stored within a regular module, and never within the UserForm Code module. You can read more about how to display a UserForm further below.

How To Toggle Between The Code And UserForm Windows

Within the VBE, you work with 2 UserForm windows or modules:

You can toggle back and forth between these 2 windows with any of the 3 following features:

• “F7” displays the Code window.
• “Shift + F7” displays the UserForm window.

• Right-click the UserForm and select View Code to go to the Code window.
• Right-click the UserForm and select View Object to go to the UserForm window.

If you want to go to the UserForm window, you can generally double-click the UserForm within the Project Explorer.

Why UserForms Work With Event-Handler Procedures

When working with UserForms, your main purpose is usually to allow users to enter information or provide other input for your macros. Therefore, your code must be able to respond to what the user does with the UserForm . You do this by relying on events.

From a broad perspective, an event is something that happens while you're working with Excel. In the case of UserForms, these are events that occur within the UserForm itself. Once the appropriate event occurs, your event-handler procedure is executed. This way, your VBA application can respond to the user's actions.

When working with UserForms, your code must respond to both UserForm and control events. The events you can work with vary depending on the UserForm control . In other words, UserForm controls have different associated events.

How To Create Event-Handler Procedures For UserForms

The general principles that apply to event-handler procedures, a topic I cover in this blog post , are applicable to the event-handler procedures you create to deal with UserForms.

The following are 3 of these principles, as they apply to UserForms:

• The UserForm or UserForm control.
• An underscore (_).
• The event that triggers the event-handler procedure.
• In some cases, an argument list.
• If you don't enter the appropriate declaration statement, following the structure I describe in #1 above, the procedure doesn't work.
• However, you don't have to learn the exact names or declaration statements for event-handler procedures. You can get the VBE to enter the appropriate declaration statement by using either of the 2 processes I describe in the following section.

How To Enter The Declaration Statement For An Event-Handler Procedure

You can get the VBE to enter the appropriate declaration statement for an event-handler procedure in the following 9 easy steps:

• If you want to work with the default event of the chosen object, you can start working on your Sub procedure. Use the statements entered by the VBE in step #4 above as a basis. If you want to work with another event, proceed to step #6.

If you're working in the UserForm window, you can use an alternative process. In this case, you can get the VBE to enter the declaration statement for the event-handler procedure in the following 2 simple steps:

How To Refer To UserForm Controls Within Your Code

UserForms and UserForm controls are objects. You generally refer to them by using their Name property . The Name property is the first in an alphabetically-organized Properties Window.

You can see how to, and why, modify the Name property of UserForm controls in a previous section.

You can see the UserForm as the parent object of the controls within it. Therefore, the basic control reference structure is as follows:

UserForm.Control

“UserForm” is the UserForm object. “Control” is the control you're referring to.

However, you usually refer to controls within event-handler procedures that are stored in the UserForm's Code module. Therefore, you can simplify the reference by omitting a reference to the UserForm . In this case, you can refer to a control as follows:

An additional alternative is to use the Me keyword to refer to the UserForm . In this case, the reference structure is as follows:

How To Display Or Load A UserForm

You can display a UserForm in the following 2 easy steps:

• Go to the appropriate module.

UserForm.Show

When choosing the procedure in which to include the Show method, consider how and when you want the user to access the UserForm . You can, for example:

• Create a procedure whose sole purpose is to display the UserForm.
• Assign that procedure to a button or keyboard shortcut.

You can also include the Show method in event-handler procedures. In such cases, the UserForm is displayed when the relevant event occurs.

You can load a UserForm, without displaying it , by working with the Load statement. In this case, the basic statement syntax is as follows:

Load UserForm

“UserForm” is a UserForm object.

When a UserForm is loaded, it exists in memory but isn't visible. Therefore, you can programmatically work with the UserForm. The user, however, can't interact with it.

Once you want to display a previously-loaded UserForm, you can use the Show method of the UserForm object.

You usually load a UserForm, without displaying it immediately, when the UserForm is complex and takes too long to load into memory . Once the UserForm is loaded in memory, you can quickly display it.

In certain cases, VBA loads the UserForm automatically without you having to use the Load statement . This is the case when you use the Show method of the UserForm object (above).

Where To Store The Macro That Displays A UserForm

You use the Show method within the procedure where you want to display the UserForm. This procedure is usually stored within a regular VBA module. You don't store the macro that displays the UserForm in the UserForm Code window itself .

To understand why this is the case, it may help if you consider the following 3 separate steps:

• First, you need to get Excel to display the UserForm.
• Once the UserForm is shown, the user can work with it.
• Depending on what the user does with the UserForm, something happens.

The code that determines what happens depending on how the user interacts with the UserForm (step #3) is stored within the UserForm. If the UserForm is never displayed, the user doesn't interact with the UserForm and this code is never executed.

Since the code within the UserForm is never executed if the UserForm isn't displayed, you can't store the code that determines when the UserForm is displayed in the UserForm Code module. If you store the code that displays a UserForm within the UserForm itself, the UserForm is never displayed.

How To Display A UserForm From The VBE

You can display a UserForm while working in the VBE by using the Run Macro (Sub/UserForm) command . You can execute the Run Macro (Sub/UserForm) command in any of the following 3 ways:

• Clicking on the Rub Macro button in the VBE toolbar.
• Going to Run > Run Macro.
• Using the “F5” keyboard shortcut.

When you execute the Run Sub/UserForm command, the Initialize event of the UserForm occurs. This option is useful for testing or preparing a UserForm you're working on.

As an alternative to the Run Sub/UserForm command, you can use the Show method of the UserForm object in a statement typed in the Immediate Window . The basic structure of such a statement is:

How Is The Control Flow When Excel Displays A UserForm

At a basic level, once the Show method of the UserForm object is called, Excel goes through the following 6-step process :

• Displays the UserForm.
• Waits for the user to work with the UserForm.
• The interaction of the user with the UserForm usually triggers an event associated to 1 of the controls within the UserForm. Therefore, the applicable event-handler procedure is executed.
• Once an event-handler procedure runs, control returns to the UserForm.
• Eventually, the UserForm is closed or dismissed by the user.
• Once the UserForm is closed, Excel continues execution of the procedure that contains the Show method of the UserForm object. This is the Sub procedure responsible for displaying the UserForm (step #1 above). In practice, however, the VBA statement that displays the UserForm is at (or near) the end of a procedure. This is because you usually store the code that is executed after the user works with the UserForm in the UserForm Code module. The code within the UserForm Code module is executed in earlier steps of this process (steps #2 to #4 above). You can read more about the UserForm Code module in a previous section of this UserForm Tutorial.

How To Close Or Hide A UserForm

There are few different ways in which a UserForm can be closed or hidden.

The first way in which a UserForm can be closed is by clicking on its Close button on the top right corner . This generally applies to all UserForms, regardless of the VBA code you use.

You can, however, monitor whether a user clicks on the Close button by using events . Clicking on the Close button triggers the following 2 events:

• QueryClose: Occurs before the UserForm is closed.
• Terminate: Occurs after the UserForm is unloaded and when the UserForm is removed from memory.

Usually, when you're creating a UserForm, you create an event-handler procedure that is triggered by the Click event of a CommandButton. This event-handler procedure normally includes 1 of the following VBA constructs :

Unload UserForm

UserForm.Hide

In both cases, if the procedure containing this statement is stored within the UserForm Code module, you can use the Me keyword . In such a case, you can always use the same statement regardless of the Name property of the UserForm. Additionally, you can replace the statements above with the following:

Unload Me Me.Hide

You generally use the Unload statement only after all the UserForm instructions and statements have been carried out . This includes retrieving the input data from the UserForm and carrying out any necessary actions. If you fail to do this, the UserForm has no real effect, other than displaying the UserForm itself.

I don't cover the topic of retrieving input from a UserForm in detail within this blog post. But you can see some code examples further below.

This topic is important because, once a UserForm is unloaded, the changes made to the UserForm's controls and their properties are reset. This results, as explained in Excel 2016 Power Programming with VBA , your code not being “able to access the user's choices after the UserForm is unloaded.”

There may be cases where you need to use the input from a UserForm after the UserForm is unloaded . You can usually store that data using a public-scope variable . Other possible solutions suggested by VBA experts Dick Kusleika and Mike Alexander in Excel 2016 Power Programming with VBA include writing the value to a worksheet cell or the Windows registry.

The UserForm.Hide method doesn't unload the UserForm from memory . It simply hides the UserForm. Therefore, you can still access and work with the user's choices and the UserForm controls.

In fact, if you use the UserForm.Hide method on an unloaded UserForm, VBA loads the UserForm and keeps it hidden.

As explained in the Excel 24-Hour VBA Trainer :

The method you choose depends on why you don't want the UserForm to be seen. Most of the time, you'll want the form cleared from memory, but sometimes, information that was entered into the form needs to be referred to the next time you show the form while the workbook has remained open.

Example: Create A Simple UserForm For Data Entry

This UserForm Tutorial is accompanied by an Excel workbook example. If you want to follow the steps I describe below, get immediate free access to this workbook by clicking the button below.

The purpose of the example UserForm is to serve as a data entry mechanism for a workbook table recording certain sales data . The entries are as follows:

• Units Sold.

The worksheet where these entries are recorded has 2 single columns. These columns match with the entries above.

These entries are the part of the basis for several tables that I use in other Tutorials, such as this one about Pivot Tables or this one about Power Query .

The example UserForm that you create below works with the following UserForm controls :

• 1 SpinButton.
• 5 OptionButtons.
• 2 CommandButtons.

In the following sections, you follow the 6-step process described at the beginning of this Tutorial to create a UserForm :

• Move or resize the UserForm controls.

The purpose of this section is to show you, step-by-step, the practical process you can follow to create a UserForm. The logic and theory behind each of these steps is thoroughly described in the appropriate section above. Please refer to those sections for more information and details.

The UserForm example you create is very simple. My purpose with this blog post is to get you started creating UserForms. Therefore, I don't dive into certain features or VBA constructs that you can use and add to improve the UserForm and make it more sophisticated or complex.

Step #1: Insert A UserForm

Insert the UserForm by following these 3 easy steps:

• Use the “Alt + F11” keyboard shortcut to go to the VBE.
• Go to the Project Explorer and right-click on the Project (workbook) you're working on. In this example, select “VBAProject(Create Excel UserForms For Data Entry In 6 Easy Steps.xlsm)”.

The Visual Basic Editor creates the following blank UserForm.

Step #2: Add Controls To The UserForm

The purpose of the example UserForm example is to serve for data entry. There are only 2 required entries:

To achieve this, use the following controls for the UserForm :

• 1 SpinButton, to specify the number of units sold.
• 1 TextBox, to display the number of units sold specified by clicking on the SpinButton.
• 1 Label for the TextBox.
• 5 OptionButtons, to choose the appropriate item. In this example, you use OptionButtons because you want to ensure that the user can select only 1 item (not several).
• 1 Frame, for the OptionButtons.
• 2 CommandButtons: A Record Entry and a Close Form button.

Add the SpinButton, TextBox, Label and Frame controls following the same 2-step process:

• Select the control in the Toolbox.
• Click on the UserForm, in the location where you want to add the control.

In the case of the CommandButtons and OptionButtons , use the following 3-step process:

• Double-click on the control within the Toolbox.
• Click on the UserForm several times, once for each time you add the control (2 for the CommandButtons, 5 for the OptionButtons).
• Click on the Select Objects command in the Toolbox to restore the mouse pointer to Selection mode.

Due to the amount of controls you include in the UserForm, start by resizing the UserForm itself . Your purpose is to make the UserForm taller. You do this by clicking and dragging on the UserForm's lower center sizing handle.

Once the UserForm is big enough to fit all the controls you want to add, and due to the layout choice, add the controls in the following order:

• OptionButtons.
• SpinButton.
• CommandButtons.

The following GIF shows the process I describe above:

In this step, don't worry too much in getting the precise location right. You organize the controls with more detail in the next step.

Step #3: Move Or Resize The UserForm Controls

Use the grid and several commands in the Format menu to arrange the controls within the UserForm . The exact commands you use vary depending on the exact situation you're in.

For this example, do the following:

• Select all the OptionButtons.
• Go to Format > Vertical Spacing > Remove.
• Go to Format > Vertical Spacing > Increase.
• Change the reference control by holding the Ctrl key while clicking on an OptionButton that has the appropriate alignment.
• Go to Format > Align > Lefts.
• Reduce the height of the Frame used to group the OptionButtons. Do this by clicking on and dragging on its lower center sizing handle.
• Move the Label, TextBox, SpinButton and CommandButtons up, so that they're closer to the lower edge of the Frame. Do this by selecting all the controls and dragging them with the mouse.
• Select all controls, excluding the SpinButton and the right-hand CommandButton.
• Select the SpinButton and the TextBox.
• Go to Format > Make Same Size > Height.
• Go to Format > Align > Rights.
• Go to Format > Align > Tops.
• Select the left-hand CommandButton and the TextBox.
• Select the CommandButtons.
• Go to Format > Horizontal Spacing > Remove.
• Go to Format > Horizontal Spacing > Increase.
• Decrease the height of the UserForm by using its lower center sizing handle.
• Decrease the width of both the Frame and the UserForm. In both cases, click and drag on their respective right center sizing handles.

The following GIF shows the 9-step process I describe above:

Step #4: Customize The UserForm And Its Controls

Customize the following 2 properties of all UserForm and UserForm controls that contain them:

• Name: The name of the UserForm or control.
• Caption: Caption text that is displayed (i) within or next to a control, or (ii) in the UserForm title bar.

The only exceptions are the TextBox and SpinButton. The TextBox and SpinButton objects don't have a Caption property.

In addition to the above, make the following specific property modifications:

• Set the Default property of the Record Entry button to True. The Default property allows you to specify whether a CommandButton is the default button in the UserForm.
• Set the Cancel property of the Close Form button to True. You can use the Cancel property to specify that a CommandButton is the Cancel button. If you do this, the user can use the Escape key as a shortcut for the Close Form button.
• Set the Enabled property of the TextBox to False. You can use the Enabled property to indicate whether the TextBox is enabled and respond to the user's actions (True) or disabled (False).
• Set the Locked property of the TextBox to True. The Locked property allows you to specify if the user can edit the entry or data within the TextBox. By locking the TextBox, you ensure that the user can't edit, add or delete data in the TextBox.

Follow the same 3-step process for all objects whose properties are modified:

• Select the object to modify.
• Go to the Properties Window and select the appropriate property.
• Double-click on the current property setting, and (when required) replace it with a new one. With some of the properties used in this example Default, Cancel, Enabled and Locked), double-clicking allows you to cycle through the available values (True and False).

The following table shows the new Name and Caption properties for each of the objects. I use “n.a.” to indicate the cases where a property isn't a member of the object.

Strictly speaking, you don't need to specify the Name property of all the objects above. This is because you don't refer to most of them (for example, the Label and Frame) within the VBA code you create in the following steps. However, as I explain above, you generally want to use meaningful and descriptive names for the objects you refer to within the code.

The following GIF shows the whole process to change the properties above:

Step #5: Assign VBA Code To The UserForm

My focus in this UserForms Tutorial are UserForms, not VBA code. My purpose is to show you how you can use some of the VBA constructs I explain in the first section of this blog post, such as the Unload statement.

Therefore, the Sub procedure examples below are very simple. You can use more sophisticated macros and find several Tutorials that focus on VBA code in the Archives .

In this step, you create the following 3 macros :

• A macro that prepares the UserForm for use by setting the default states of the TextBox, SpinButton and OptionButtons in the UserForm.
• A macro that records entries.
• A macro that connects the TextBox to the SpinButton, so that changes to the SpinButton are reflected in the TextBox.

I explain the VBA code used to display and close the UserForm in the following steps (#6 and #7).

The 8-step process you can follow to enter the VBA code for the UserForm is as follows:

• Takes you to the Code window.

• Enter the VBA code for the Sub procedure triggered when the user clicks on the Record Entry CommandButton. This is the main Sub procedure and carries out most of the work you require.

• Enter the VBA code for the Sub procedure triggered when the user clicks on the arrows of spinButtonUnits.

• Enter the VBE code for the macro triggered when the UserForm is initialized.

Let's look at the VBA code for the 3 procedures you create in this step:

Sub Procedure #1: Private Sub UserForm_Initialize()

The following is the VBA code for the macro executed when the UserForm is initialized :

This macro sets the default values and selections for several controls within the UserForm , as follows:

• Value of TextBox: 0.
• Value of SpinButton: 0.
• Selected OptionButton: Surface Studio.

You can also manipulate these properties through the Properties Window (step #4 above).

One of the main reasons I prefer to set these default property values through VBA code is that this allows me to reuse the code within this Sub procedure to reset the UserForm and prepare it for a new entry. In fact, the last statement in Sub procedure #2 below calls the UserForm_Initialize macro for these purposes.

Let's look at each line of code within this macro:

Lines #1 And #5: With Me | End With

Opening and closing statements of a With… End With block.

The statements within the block (lines #2 to #4) work with the object specified in the opening line of the block (Me). You use the Me keyword to refer to the UserForm itself.

Line #2: .textBoxUnits.Text = 0

Sets the default value of the TextBox to 0. This default number of units (0) is recorded when the user fails to specify one through the SpinButton control.

You achieve this by setting the Text property of the TextBox (.textBoxUnits.Text) to 0.

Line #3: .spinButtonUnits.Value = 0

Sets the default value of the SpinButton to 0. This value matches the default value of the TextBox (line #2 above).

You do this by setting the Value property of the SpinButton to 0. The Value property of a SpinButton is an integer.

Line #4: .optionButtonSurfaceStudio.Value = True

Selects (by default) the OptionButton that corresponds to the Surface Studio.

You achieve this by setting the Value property of the first OptionButton (Surface Studio) to True. The Value property allows you to specify whether the OptionButton is selected (True).

Private Sub commandButtonRecordEntry_Click()

The following is the VBA code within the Sub procedure executed when the user clicks the Record Entry button:

This Sub procedure goes through the following process :

• Find the first empty row in the worksheet where the entered data is recorded.
• The Item chosen with the OptionButtons of the UserForm is recorded in column A.
• The number of units displayed in the TextBox of the UserForm is recorded in column B.
• Resetting the values of the TextBox and SpinButton.
• Selecting the default OptionButton (for Surface Studio).

The macro doesn't carry any data validation checks, nor handles any possible errors. For example, it doesn't check whether the user has entered the number of units using the SpinButton. In this example, those checks are mostly handled by the SpinButton and the properties of the TextBox you modify in step #4 above, as follows:

• The user can't enter data directly in the TextBox. This is a consequence of the values you set for the Enabled (False) and Locked (True) properties of the TextBox in step #4 above.
• The number of units displayed on the TextBox is dependent on the SpinButton. In other words, the user specifies the number of units through the SpinButton. The TextBox simply displays the current number of units. You achieve this through an event-handler procedure triggered by the Change event of the SpinButton object. You can find an example of such code below (Sub procedure #3).

This Sub procedure doesn't close the UserForm . Therefore, the user can make several entries at any time without having to continually open the dialog box. The user can, however, close the dialog box at any time by clicking on the Close or Close Form buttons. See step #7 below to see how to close the dialog box with the Close Form button.

Let's go through each line of code to understand how the macro works:

Lines #1 And #2: Dim myWorksheet As Worksheet |Dim myFirstBlankRow As Long

Use the Dim keyword to declare 2 variables:

• myWorksheet: An object variable. Represents the worksheet where the data is recorded.
• myFirstBlankRow: A variable of the Long data type . Represents the number of the first blank row in the table where the data is recorded.

Line #3: Set myWorksheet = Worksheets(“Excel UserForm data entry”)

Uses the Set keyword to assign a worksheet (Excel UserForm data entry) to an object variable (myWorksheet).

Lines #4 And #21: With myWorksheet | End With

Opening and closing statements of a With… End With block. The statements within the block (lines #5 to #20) work with the object specified in the opening line of the block (myWorksheet).

Lines #5: myFirstBlankRow = .Cells.Find(What:=”*”, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1

Finds the first empty row in myWorksheet and assigns its number to the myFirstBlankRow variable.

The process followed to find the first empty row in the worksheet is as follows:

• The number of the last cell with data in the worksheet is found (.Cells.Find(What:=”*”, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row).
• A value of 1 is added. This addition returns the number of the row below the last cell with data or, in other words, the first empty row in the worksheet.

Let's analyze the portion of the statement that finds the last row in the worksheet. I explain several different ways to find the last row in a worksheet in this VBA Tutorial .

• The Worksheet.Cells property (.Cells) returns all the cells within myWorksheet.
• What: “*”. The data you're searching for. The asterisk (*) acts as wildcard. Therefore, the Find method searches for any character sequence.
• LookIn: xlFormulas. The type of data you're searching in. xlFormulas results in VBA searching (looking) in the cell formulas.
• LookAt: xlPart. Specifies whether the Find method matches the cell contents in their entirety, or just a part of them. xlPart results in Excel doing a partial match.
• SearchOrder: xlByRows. Specifies whether the search is carried out by rows or by columns. Since you're looking for the last row, you set this to xlByRows.
• SearchDirection: xlPrevious. The direction (next or previous) in which the search is carried. In this case, you set the argument so that Excel searches for the previous (xlPrevious) match.

Once the last row in the worksheet is identified, 1 is added (+ 1). The resulting value is assigned to the myFirstBlankRow variable (myFirstBlankRow =).

Lines #6 And #19: With .Cells(myFirstBlankRow, 1) | End With

Opening and closing statements of a With… End With block. The statements within the block (lines #7 to #18) work with the object specified in the opening line of the block (.Cells(myFirstBlankRow, 1)).

Let's analyze the object to which statements refer to:

• The Cells property (.Cells) works with the object specified in the opening statement of the outer With… End With block (line #4 above). That object is myWorksheet.

Lines #7 And #18: Select Case True | End Select

Opening and closing of a Select Case statement.

Executes one of the groups of statements within this block of code (lines #8 to #17) based on the value taken by an expression. The group of statements that's executed is determined by finding which expression matches the test expression.

The test expression is set in the opening line of code (#7) of the block. In this case, a Boolean value: True (Select Case True).

Lines #8 Through #17: Case optionButtonSurfaceStudio.Value | .Value = “Surface Studio” | Case optionButtonSurfaceBook.Value | .Value = “Surface Book” | Case optionButtonSurfacePro4.Value | .Value = “Surface Pro 4” | Case optionButtonXboxOneS.Value | .Value = “Xbox One S” | Case optionButtonXboxOne.Value | .Value = “Xbox One”

Line #8, #10, #12, #14 and#16 are the expressions against which VBA seeks to match the test expression (True) from line #7 above. The statement that VBA executes is the one following the expression that returns True and, therefore, matches the test expression.

The following table shows the tested expressions along with the statement that's executed when that expression returns True:

Notice the following:

• The structure of all the groups of statements above is identical.
• The OptionButton.Value property returns True if the OptionButton is selected.
• The OptionButtons used in this example are grouped. Therefore, when the user selects 1 option, the other options are automatically de-selected. In other words, only 1 OptionButton can be selected.
• Because of #1 and #2 above, the expression that matches the test expression is that which corresponds to the OptionButton selected by the user.
• The Value property (.Value) works with the object specified in the opening statement of the With… End With block (line #6). That object is the cell where the next entry for Item goes.
• The executed statement sets the Value property of the relevant cell to be equal to a string. This string is the name of one of the Items.

Overall, the consequence of the above structure is that the data recorded in the Excel worksheet depends on the OptionButton the user chooses, as follows:

Line #20: .Cells(myFirstBlankRow, 2).Value = Me.textBoxUnits.Value

Records the number of units within the No. of units sold TextBox in the appropriate worksheet cell.

Let's see how this works:

• The statements sets the Value property of a cell (.Cells(myFirstBlankRow, 2).Value =) to be equal to the Value property of the UserForm TextBox (Me.textBoxUnits.Value). The Value property of the TextBox is determined by the SpinButton. This part of the process is controlled by the Sub procedure #3 I explain below.
• The Cells property (.Cells) works with the object specified in the opening statement of the With… End With block (line #4). That object is represented by myWorksheet.

Line #22: UserForm_Initialize

Calls the UserForm_Initialize Sub procedure. I explain this event-handler procedure in more detail above (Sub procedure #1).

The UserForm_Initialize macro sets the default values and controls of certain UserForm controls, as follows:

• TextBox: 0.
• SpinButton: 0.
• OptionButton selection: Surface Studio.

In other words, by calling the UserForm_Initialize Sub procedure, you reset the values and selections within the UserForm to their defaults. This ensures that the UserForm is ready for a new entry.

Sub Procedure #3: Private Sub spinButtonUnits_Change()

The following is the VBA code within the Sub procedure executed when the user clicks on any of the arrows of the SpinButton control:

This macro consists of a single VBA statement:

textBoxUnits.Value = Me. spinButtonUnits.Value

This statement sets the Value property of the TextBox object (textBoxUnits.Value) to be equal to the Value property of the SpinButton object (Me.spinButtonUnits.Value). In other words, it:

• Connects the TextBox to the SpinButton.
• Ensures that the value displayed in the TextBox is determined by the SpinButton.

Step #6: Display The UserForm

For this example, get Excel to display the UserForm in the following 3 simple steps:

• Go to a module different from the UserForm's code module.
• Create a Sub procedure that calls the Show method of the UserForm object.
• Assign a keyboard shortcut to the displayUserForm macro.

Let's go through each of these steps:

Step #1: Go To A Module

In this example, store the macro that displays the UserForm in a standard module (Module1).

If necessary, insert a new module by, for example, right-clicking on the workbook within the Project Explorer and going to Insert > Module.

Step #2: Create A Sub Procedure That Calls The Show Method Of The UserForm Object

Create the following macro (displayUserForm):

displayUserForm consists of the following single statement:

userFormSalesEntry.Show

When executed, this macro displays the userFormSalesEntry you created in the previous 5 steps.

Step #3: Assign A Keyboard Shortcut To The displayUserForm Macro

For this example, execute the displayUserForm Sub procedure with a keyboard shortcut. Make this assignment in the following 4 easy steps:

• Open the Macro dialog box by, for example, using the “Alt + F8” keyboard shortcut.

Step #7: Close The UserForm

A user can close the dialog box example by clicking on the Close Form button. You can enter the code to close the UserForm when the user clicks this button in the following 4 easy steps:

• Go to the VBE by, for example, using the “Alt + F11” keyboard shortcut.

• Takes you to the UserForm's Code window.

• Enter the VBA code for the Sub procedure triggered when the user clicks the Close Form button. In this example, this procedure includes a statement with the Unload statement.

The following is the code within the Sub procedure executed when the user clicks the Close Form button:

This Sub procedure has the following single statement:

The Unload statement unloads the UserForm from memory. This results in the dialog box being dismissed.

UserForm For Data Entry In Action

You can enter data with the UserForm example in the following 5 simple steps:

• Use the “Ctrl + Shift + A” keyboard shortcut.

• Enter the number of units sold by using the SpinButton.
• Choose the appropriate Item.

• Repeat step #3 as many times as required, depending on the number of entries you want to make.

The GIF below shows the UserForm in action. Notice how:

• Every time I click the Record Entry button, the entry is recorded in the table.
• When I click the Close Form button, the dialog box is closed.

After reading this UserForms Tutorial, you have the knowledge and resources to start creating UserForms in Excel now . The following are some of the topics you learned about :

• What is a UserForm, and why are they useful.
• The simple 6-step process you can use to create a UserForm for data entry.
• How to insert and customize UserForms.
• What are UserForm controls, and how you work with them. This includes, among others, how to add, select, move, resize, customize and remove controls.
• How to create the macros that power your UserForms. This includes the VBA code you use to both: (i) display, load, close and hide UserForms, and (ii) respond to the user's actions within the UserForm.

In the second section of this UserForms Tutorial, you saw a practical example. In this example, you followed the easy 6-step process to create a simple UserForm for data entry . Remember that this blog post is accompanied by an Excel workbook example containing this UserForm example. You can get immediate free access to this workbook by clicking the button below.

In practice, you're likely to work with more complex UserForms that (i) gather more data, (ii) use additional controls, and (iii) work with more complex VBA code. In most cases, the basic principles and constructs you've learned about by reading this UserForms Tutorial continue to apply .

Books Referenced In This UserForms Tutorial

• Alexander, Michael and Kusleika, Dick (2016). Excel 2016 Power Programming with VBA. Indianapolis, IN: John Wiley & Sons Inc.
• Goldmeier, Jordan (2014). Advanced Excel Essentials. New York City, NY: Apress.
• Mansfield, Richard (2016). Mastering VBA for Microsoft Office 2016. Indianapolis, IN: John Wiley & Sons Inc.
• Urtis, Tom (2015). Excel VBA 24-Hour Trainer. Indianapolis, IN: John Wiley & Sons Inc.

I publish a lot of Tutorials and Training Resources about Microsoft Excel and VBA . Here are some of my most popular Excel Training Resources:

• Free Excel VBA Email Course
• Excel Macro Tutorial for Beginners
• Excel Power Query (Get and Transform) Tutorial for Beginners
• Excel Keyboard Shortcut Cheat Sheet
• Excel Resources

Free All-in-One Office Suite with PDF Editor

Edit Word, Excel, and PPT for FREE.

Read, edit, and convert PDFs with the powerful PDF toolkit.

Microsoft-like interface, easy to use.

Windows • MacOS • Linux • iOS • Android

• Articles of Excel
• Excel-How-to

​How to Add a Button in Excel (Easy & Quick)

People found many difficult tasks in their workplace data management projects, especially while dealing with data in Excel in bulk. So, they can add a button in excel to resolve most of their problems in one click.

In this article, you will come to know some learning aspects about how to add a button in excel without a macro and with how to add a button in excel with a macro – if you want or need it. You can have the most simple and easy steps ahead to follow to solve more than half of the problems while using Excel data dealing.

Types of Button Available in Excel

In Excel, you can have the opportunity to hyperlink the Button to specify some of the entries in the data to manage the entire sheet in an organized form. Therefore, it is necessary to understand how many types of Buttons are available in Excel.

Basically, Excel has two types of Buttons available that are Form Controls and ActiveX Controls, that can add convenience to your work.

What are Form Controls?

Form Controls are the compatible and original Excel controls used in the XLM macro sheet. Moreover, you can use the form controls to give a reference, interact, or redirect someone to the specified location, making the interacting data cell clickable without using the VBA code.

There are buttons in the Form Control below; you can see the screenshots for your comprehensive understanding.

What are ActiveX Controls?

ActiveX Controls are also found in Excel features used in worksheet forms with or without macro or VBA code. If you want to have more flexible design requirements in your spreadsheet containing data and other information in bulk, you can use the ActiveX Form Controls to manage it in an organized and professional way by clicking on a clickable section of the interactive cell in the worksheet.

2. Preparation Before Adding Buttons in Excel

To work up to snuff in the management and features of data in excel and to create a button in excel, you should first get some preparations before you add buttons in the excel workbook.

In this article, you will come to know the basics and difficult techniques in a very simple and easy method, which is helpful for you during implantation in your own spreadsheet data at a desired location.

Enable/Visible Developer in Excel Ribbon Before Adding Buttons in Excel

First, before you go to add a button in excel, it is necessary to insert the Developer option in the top Excel Ribbon. For this purpose, you can get a simple guide from below mentioned comprehensive steps:

Step 1: In the Excel file section, directly go to the last portion containing the Options tab.

Step 2: In the Options section, go to the Customize Ribbon in Excel once a dialogue box will appear to you.

Step 3: Next to it, a dialogue box will appear to Customize the Ribbon that can add a Developer option by marking it tick.

Step 4: Mark the box of the Developer section to allow it to appear on the Excel top ribbon location.

Step 5 : After enabling the Developer in the Excel ribbon, you can check it after pressing; you will see there are options that can help you easily add a button in Excel.

3. How to Add Form Buttons in Excel

There is a simple step-by-step method to add a button in Excel that can improve the functionality and productivity of your work.

Add Form Buttons in Excel (Using Form Controls)

Step 1 : In the Developer section, go to the Insert icon that falls under the Controls group.

Step 2: In the Form Controls, there is a Button option at first; click on it in drag-drop style.

Step 3: After selecting the designation name of the macro in the Assign Macro box, click OK.

Step 4: You can set the macro in the Assign Macro dialogue box and click OK.

Step 5 : You can format the styling, formatting, size, color, or whatever changes you want to make; you can simply go to the Format Control option.

Step 6 : When you click on the Format Control, a window will open where you can make changes in font or other formatting.

Step 7 : Next to it, you can freeze the movement by going to the Properties tab, marking the section Don’t move or size with cells, and clicking OK very next to it.

Add a Button (Form Control)

If you want to add a button in the data easily Excel sheet with form control, here you can have a few simple steps that you can follow to add a button in Excel without macro in the easiest way.

The first step you need to consider is enabling the Developer tab, which is hidden by default in Excel.

Step 1 : In the Developer section, there is the Controls group; click Insert and further click on Button under the section of Form Controls.

Step 2: Where you want that Button to appear, you can click here and manage the Assign Macro pop-up window.

Step 3: Once you are done with the settlement of Assign Macro, go with the OK.

Step 4: Now right-click on the Button and select Format Controls to specify the control properties on the selected location of a workbook.

4. How to Add ActiveX Buttons in Excel

If you are finding ways to add the ActiveX buttons in Excel to enable the command button in your Excel workbook sheet containing a lot of sheets and make your work look professional. So, you have to simply follow these steps to add ActiveX buttons in Excel, which can also be helpful for you to add a button in Excel with a macro. Here you go!

Step 1 : In the Developer section, there is the Controls group; go and click Insert and then click the Command Button option under the ActiveX Controls section.

Step 2: Click on the desired location where you want to add the command button.

Step 3 : In the Controls section, just click on the View Code. Make sure that the Click process is drop-down on the right list of a spreadsheet. You can run the macros when you set the Command Button 1_Click in the box that appears. Simply select the message and make desirable changes in the ActiveX Buttons in Excel.

Further, if you want to include the macro Button in your Excel sheet, you can simply consider the below-mentioned steps to improve your data in an organized way. Please let’s have a look below:

Step 1: Go to the Developer section; in the Control group section, click Insert, and in the Insert section, click Button under the Form Controls.

Step 2: Select the desired location in the worksheet where you want to appear; the Button in the upper-left corner, as the Assign Macro screen will appear in front of you.

Step 3: Once you assign a macro button, now click on OK.

Step 4: You can click on Format Control to specify the control properties in the selected section by right-clicking on the Button.

5. Best Free Alternative to Microsoft Excel - WPS Office

WPS Office suite is the most reliable innovation that is greatly helpful for everyone, providing feature-rich programs Excel, Word, PowerPoint, PDF, and more, becoming a market leader in increasing office productivity.

WPS Office is a free tool giving reliable all-in-one services currently has more than 494 million active users worldwide – representing how significant and popular WPS Office is. It is one of the best free alternatives to Microsoft Office, providing additional features without advertisements.

Advantages of WPS Office Suite

Free to Use

Rich Functions

Additional Tools

Best Editor

Strong Compatibility

User-Friendly Interface

Download WPS Office Suite

Step 1: You can visit the main official site of the WPS office suite: https://www.wps.com/ and click on the Free Download option.

Step 2: Once done with clicking on download, it asks for installation on your PC, so click on the Install Now option after agreeing on the WPS office policy license.

Step 3 : Now, WPS Office will start downloading in your device system within in few seconds.

Step 4 : The main working screen will appear after the WPS Office is launched on your PC, Mac, or Android.

Step 5: In the main home screen of the WPS Office suite, you can see that there are additional tools that can help you process easily, making your work more professional.

Use Word, Excel, and PPT for FREE, No Ads.

Edit PDF files with the powerful PDF toolkit.

Microsoft-like interface. Easy to learn. 100% Compatibility.

Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

Can I add a button to run the VBA code?

Yes, you can easily add a button to run VBA code in your Excel spreadsheet. In your Excel workbook, if it uses VBA code and you want others to run that code easily, then you can use a form control button, which is a great option.

Here are some simple steps that you can opt to add a button in the Excel sheet to run VBA code smoothly:

Step 1 : In the ribbon, go to the Developer section.

Step 2 : Simply click on the Insert tab in the Controls section.

Step 3 : Press on Button form control from the menu section.

Step 4 : Now right-click by holding the mouse and go for the drag and release to create your Button to run.

Step 5: You will see a pop-up of Assign Macro, so now go to Select the VBA Procedure that you want to run from your desired Button.

Step 6 : Click the OK option.

Can I assign multiple macros to a single button?

Yes, you can easily assign the multiple macros to a single button with VBA code; just do with the following steps mentioned below:

Step 1: After clicking on Developer, go to the Insert tab and click on Button (Form Control).

Step 2: By dragging the mouse, draw the Button on your current selected sheet. Also, in the Assign Macro box, click on the OK Button.

Step 3 : To open Microsoft Visual Basic for Applications, you can hold down the Alt + F11 key.

Step 4: Go to Module in the Insert tab and paste the code in the Module Window.

Step 5 : Click on Save. Now right-click the Button, then choose Assign Macro.

Step 6 : In the Assign Macro box, select the Button Click option with multiple macro options to add; now insert the name you want to have here and click on the OK Button.

Step 7: Whenever you click on the Button, the specified macros will appear on a single button after each trigger.

Be Ahead of the Curve

To make productive changes in your professional and business level work, you may interact with the frequent use of spreadsheets, where organizing the difficult tasks by the easiest method is necessary.

And it can happen when you learn how to add a button in Excel in 2023 – a fast-paced technological world.

To get better experience in learning how to add a button in Excel without macro, you may also want to know how to add a button in Excel with macro; both problems can easily be solved when you consider the most reliable and accessible tool – WPS Office Excel.

WPS Office is a free office suite containing Word, Excel, PowerPoint, PDF, and other essential features that help every user to get aided by it. Therefore, if you want to hyperlink the Button in Excel easily, you should install the WPS Office on your PC or Mac and be ready to benefit from it; solving your problem with WPS Office Excel – plays a significant role in boosting productivity and efficiency of work.

• 1. How to Calculate Profitability Index in Excel (Easy&Quick)
• 2. How to Remove Last Word from Cell in Excel (Easy & Quick)
• 3. Enabling Dark Mode in LibreOffice Writer_ A Quick and Easy Guide
• 4. How to Fix Escape Button Not Working in Excel [Step-by-Step]
• 5. How to Create a Pivot Table in Excel (Easy & Quick)
• 6. 6 Ways to Use Formulas in Excel for Multiplication (Easy&Quick)

15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.