Refresh a Userform in VBA

There are virtually limitless possibilities when working with Visual Basic for Applications (VBA). When you start dealing with it, the first thing you will probably be familiarized with is Modules.

However, one more very useful thing that is used more often than not is User Forms. In the example below, we will show how to create user forms and how to reset or refresh the data in them.

How to Create User Forms

First things first, we need to show how to create User Forms. They are located in the same place as the Modules in VBA, so all you need to do is click the combination of ALT + F11 on your keyboard, and then the VBA will appear. Once there, you need to right-click on the white window on the left side of the screen, and click Insert >> UserForm:

Graphical user interface, application

Description automatically generated

After we do that, a window will appear on the right side:

A picture containing graphical user interface

Description automatically generated

This window mimics the one that we will have on our worksheet. To manipulate the User Form easier, we will go to the View tab in the VBA and click on the Toolbox:

Graphical user interface

Description automatically generated

Once we click on that, we will be presented with the Toolbox window:

Graphical user interface, text, application, Teams

Description automatically generated

Now we can go ahead and create the User Form.

We go on a Toolbox and click on the Label- the second item in the list:

Graphical user interface, application, email, Teams

Description automatically generated

We will get the drawing pointer, and we can draw the Label in our User Form. We will create two Labels: Name and Martial Status:

Calendar

Description automatically generated with medium confidence

We will then insert a TextBox (third in the list in the Toolbox) right next to the Name, and two Option Buttons (seventh in the list in the Toolbox) right next to the Martial status. This is what our User Form will look like:

A picture containing calendar

Description automatically generated

Refresh a User Form

You can notice that our User Form does not have any code in it, and we need to attach something to it. Before doing that, we will add a Command Button (second on the list in the second row of the Toolbox) in the User Form and name it simply Refresh:

Graphical user interface

Description automatically generated

User Form is interactive, and we can add a code or action to any specific part of it. But we also need to find a way to run it, and one of the ways to do it is to create ActiveX Controls in the worksheet. We will go back to our sheet, and then go to the Developer tab >> Controls >> Insert >> ActiveX Controls >> Command Button:

Graphical user interface, application, table, Excel

Description automatically generated

We will now have an option to place a Command Button anywhere we want. Once we do, we will change its name by right-clicking on it, and changing the caption in Properties:

Graphical user interface, table

Description automatically generated

Now we need to right-click on the button again and choose the option View Code:

Graphical user interface, application, table, Excel

Description automatically generated

We will immediately be presented with the window where we can insert our code. What is so great about it, it is that we get the suggested Subroutine, which is that we will get some actions when we click on the button. This is the code we need to insert to present our User Form on click:

We need to load UserForm1 (which is the name of our form) and then show it.

For the next thing, and to finally define our refresh, we need to go back to the User Form in the VBA and then click on the Refresh button that we created. After that, we will be directed to the window where we can define actions. We will be suggested with the Subroutine, and we will insert the following code:

This is what our code looks like in the module:

Graphical user interface, text, application, email

Description automatically generated

It is important to note that we need to define refresh for every single item, whether it be a command button, text, or anything similar. We have a Text Box and two Option Buttons in our User Form, so our code takes care of all of these things.

First part:

Calls for Me (pretty simple to call User Form this way) and declares the value of the Text Box to be nothing on click.

Second part:

Uses For Next Loop to cover all possible Option Buttons and to assign with the False values. If we have inserted null values, the Command Buttons would be greyed out, not empty after refresh.

At this point, we have everything set up. We can go to the worksheet and click on our button:

Graphical user interface, application, table, Excel

Description automatically generated

We will type in anything in the Text Box and choose any value in Command Button:

Graphical user interface, application

Description automatically generated

All we need to do to delete the data now is to click on the Refresh button. When we do, this is what we will end up with:

Graphical user interface, application

Description automatically generated

It is just like we never put any values into the User Form.

Remember: It is of high importance to cover all possible scenarios and to return every possible Text Box, Command Button, or anything where the values might be stored to the original value, i.e. nothing!

Posted in vba