VBA Tutorial P6 – What is a UserForm and how can you create one?

Hey beautiful people! How are you today? Good to go?

Great! So as the title says, in this post we’re going to talk about UserForms.

A UserForm is a customizable window similar to a user interface. Actually, it is THE user-interface for any application you develop in VBA that requires a more complex way to handle the way a user interacts with it. In short, it’s a pop-up.

Here is how one looks like:

Image 1

You might wonder why you need an UserForm since you already know how to connect macros with buttons in Excel and since Excel already has cells in which you write stuff. And that’s a legit question.

The answer is that you need UserForms when the things get too complicated to handle them in a worksheet. For example, if you need to create a selenium-based macro that downloads images and you need to review each image to see if it’s ok or not, you can have a UserForm that loads each image and it waits for your OK and based on your input, that image can be placed in the OK folder or in the Not OK folder (of course, you could develop a machine learning algorithm that does that, but that’s something we’ll cover in a different tutorial). You can even combine this with some audio alert to get your attention when needed.

Another advantage of UserForms is that they don’t stop the code (if the ShowModal setting is set to False). This is useful when you need to launch another macro/function when the UserForm is shown.

But enough theory! Let’s me show you how can you create one.

How to create a UserForm

The steps required to create a UserForm are similar with the ones required to create a new Module. First, hit ALT + F11. Then go to Insert -> UserForm.

Image 2

Now you should have UserForm1 (similar with the image below).

Image 3

Note: If you face difficulties with creating the UserForm, go through the procedure if creating a Module mentioned in my post regarding how to create a simple VBA macro because that’s where I wrote some details regarding other settings.

You can make it bigger or smaller by clicking and dragging from the corners. And for adding buttons and labels and other stuff, you need to use the Toolbox (it should be active by default, but if not, click on the top-center button, as shown in the image below, and it will appear).

Image 4

Now let’s get through each element a little bit to tell you what can you do with them.

Image 5

1 – Select Object – this allows you to select any created object from within the UserForm and do whatever you want with it (e.g. move it, delete it, resize it and so on). It acts like a cursor within the form and it is the default selected item from the Toolbox;

2 – Label – this allows you to enter some pre-defined text that shouldn’t necessarily be directly accessed by the end-user. It acts like a header for something (for example, if you want to let the users know what the next box is about, you would use a label for add that);

Image 6

3 – TextBox – this object allows the user to insert some text that can then be fetched by a VBA macro and processed in any way it is required (notice that I used a Label too, as there was no other way to tell you what object is that);

Image 7

4 – ComboBox – this object allows you to create a dropdown with a list of required elements (you can even mark an item to appear in the field by default just as it has been selected by the user). Once an item is selected, you can fetch it with a VBA macro and process it in the required way;

Image 8

5 – ListBox – this object allows you to set a list from which the user can select one or more items that can then be fetched and processed through VBA. If it’s only one item that needs to be selected, a ComboBox would be a better fit as it is not as big as a ListBox (this is a matter of personal choice, of course), but if multi-items need to be selected from the list, you need to go with a ListBox;

Image 9

6 – CheckBox – this object allows you to… well… I think the name says it all;

Image 10

7 – OptionButton – an option button is basically a radio button. Checking one will keep the others unchecked;

Image 11

8 – ToggleButton – this acts like an on/off button for whatever you want (it is dependent of the code you write behind it). You can, of course, customize both options;

Image 12
Image 13

9 – Frame – a frame is a container for other objects. It is not transparent, even though it might seem like it. If you have objects behind the frame, they won’t appear in the front. You can use it to give a stylish look to your UserForm;

Image 14

10 – CommandButton – this object’s sole purpose is to launch macros. It is similar to the buttons presented in the post regarding how to connect a macro with a button;

Image 15

11 – TabStrip – this object is used to apply the same functionality to different sets of data. You can assign a set of data (e.g. a range or a sheet) to each tab and then you can have a button (which, by default, is shared between all tabs) to perform something (for example, you can have a button that can tell you the number of empty rows within that range). It is useful because it enables you to apply the same code to different ranges quickly;

Image 16

12 – MultiPage – this object is used to have multiple functionalities, all grouped together into pages. A UserForm page is just like a page or a section in a website. Each page can have different other objects within and the objects from one page will become invisible once you select a different page;

Image 17

13 – ScrollBar – this object is used to “scroll” in a range of numbers of dates. In a way, is somehow similar with a ComboBox where you can select something from a list, but here you don’t have a pre-defined list of values, but a range and you can scroll through that range. It is used together with another element for set and display (e.g. Label or TextBox etc.);

Image 18

14 – SpinButton – this object is used to slightly increment or decrease a value that it is shown in another element. It is similar with the ScrollBar, with the difference that the change is one step at a time (e.g. +/- 1);

Image 19

15 – Image – this object is used to add an image into it (yes, I know, I’m shocked too!). It’s the only way to display an image in a UserForm, that’s requires a separate object.

Image 20

That’s it! These are all the elements in a UserForm!

Next post -> VBA Tutorial P7 – How to connect macros with UserForm elements?

Other posts in this series -> VBA Tutorial for Beginners


PS: If you want to support this website, you can do it through the Show Your Support page!

Leave a Reply