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:
After we do that, a window will appear on the right side:
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:
Once we click on that, we will be presented with the Toolbox window:
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:
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:
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:
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:
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:
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:
Now we need to right-click on the button again and choose the option View Code:
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:
|
1 2 3 4 |
Private Sub UserForm_Click() Load UserForm1 UserForm1.Show End Sub |
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:
|
1 2 3 4 5 6 7 |
Private Sub CommandButton1_Click() Me.Controls("TextBox1").Value = "" Dim i As Long For i = 1 To 2 Me.Controls("OptionButton" & i).Value = FALSE Next i End Sub |
This is what our code looks like in the module:
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:
|
1 |
Me.Controls("TextBox1").Value = "" |
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:
|
1 2 3 4 |
Dim i As Long For i = 1 To 2 Me.Controls("OptionButton" & i).Value = FALSE Next i |
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 the button:
We will type in anything in the Text Box and choose any value in Command Button:
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:
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!