Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
Creating a Userform
2.1.
Add the Controls
2.2.
Show the Userform
2.3.
Assign the Macros
2.4.
Test the Userform
3.
Userforms and Range
4.
Various Programs using Userform
4.1.
Progress Indicator
4.2.
Currency Converter
5.
Interactive Userform
6.
FAQs
7.
Key Takeaways
Last Updated: Mar 27, 2024

Userforms

Author Naman Kukreja
0 upvote
Master Python: Predicting weather forecasts
Speaker
Ashwin Goyal
Product Manager @

Introduction

Data is an essential key to success in this technologically enhancing world, and if you want to be an active part of it, you should be good at dealing with data. Dealing with data involves monitoring, analyzing, gathering, consolidating, and connecting the data. 

Excel is one of the best tools to work with data. Entering data sometimes becomes very lengthy as many of the new users are not familiar with how to add the information quickly. So is there any way that makes this way of entering data easy?

The answer to the above question is yes. We can achieve this by using user forms in excel VBA. So let's get started with our topic without wasting anytime further.

Creating a Userform

Userform is the custom-built dialog box that makes the user data entry more accessible, comfortable, controllable, and straightforward. Here you will learn how to create user forms, show user forms, and much more.

Add the Controls

Follow the steps given below to add controls to the userform.

  • Open the Visual Basic Editor.
  • Inset a userform by clicking on insert and then selecting the userform option. Now your screen will be like the image given below:
     

  • The controls indicated in the table below should be added. When you're done, the output should look like the Userform image you saw before. Create a text box control, for example, by selecting TextBox from the Toolbox. After that, drag a text box onto the Userform. When you get to the Car frame, remember to sketch it first before inserting the two choice buttons.
  • Change the controls' names and captions to match the table below. In the Excel VBA code, names are utilized. The captions that display on your screen are called captions. Changing the names of controls is an excellent idea. This will make it simpler to read your code. Click View, Properties Window, and click on each control to alter the names and captions.

Show the Userform

We will place a command button on the worksheet to show the userform, and in the command button, we will add the following line:

Private Sub CommandButton1_Click()

 
DinnerPlannerUserForm.Show

 
End Sub

Now we will create a sub userform_initialize, and do so, follow the steps given below:

  • Open Visual Basic Editor.
  • Then right, click on userform, and select the view code option.
  • Now select initialize from the right list and userform from the left list, and now add the following code in the window:
Private Sub UserForm_Initialize()

 
'Empty NameTextBox
NameTextBox.Value = ""

 
'Empty PhoneTextBox
PhoneTextBox.Value = ""

 
'Empty CityListBox
CityListBox.Clear

 
'Fill CityListBox
With CityListBox
    .AddItem "San Francisco"
    .AddItem "Oakland"
    .AddItem "Richmond"
End With

 
'Empty DinnerComboBox
DinnerComboBox.Clear

 
'Fill DinnerComboBox
With DinnerComboBox
    .AddItem "Italian"
    .AddItem "Chinese"
    .AddItem "Frites and Meat"
End With

 
'Uncheck DataCheckBoxes
DateCheckBox1.Value = False
DateCheckBox2.Value = False
DateCheckBox3.Value = False

 
'Set no car as default
CarOptionButton2.Value = True

 
'Empty MoneyTextBox
MoneyTextBox.Value = ""

 
'Set Focus on NameTextBox
NameTextBox.SetFocus

 
End Sub

Assign the Macros

Now, we will set up the operation that will happen when we click on the command button.

  • Open Visual Basic Editor, then click on DinnerPlannerUserForm.
  • Now add the following line of code after double-clicking on the Money spin button:
Private Sub MoneySpinButton_Change()

 
MoneyTextBox.Text = MoneySpinButton.Value

 
End Sub
  • Now click twice on the Ok button and add the following lines of code:
Private Sub OKButton_Click()

 
Dim emptyRow As Long

 
'Make Sheet1 active
Sheet1.Activate

 
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

 
'Transfer information
Cells(emptyRow, 1).Value = NameTextBox.Value
Cells(emptyRow, 2).Value = PhoneTextBox.Value
Cells(emptyRow, 3).Value = CityListBox.Value
Cells(emptyRow, 4).Value = DinnerComboBox.Value

 
If DateCheckBox1.Value = True Then Cells(emptyRow, 5).Value = DateCheckBox1.Caption

 
If DateCheckBox2.Value = True Then Cells(emptyRow, 5).Value = Cells(emptyRow, 5).Value & " " & DateCheckBox2.Caption

 
If DateCheckBox3.Value = True Then Cells(emptyRow, 5).Value = Cells(emptyRow, 5).Value & " " & DateCheckBox3.Caption

 
If CarOptionButton1.Value = True Then
    Cells(emptyRow, 6).Value = "Yes"
Else
    Cells(emptyRow, 6).Value = "No"
End If

 
Cells(emptyRow, 7).Value = MoneyTextBox.Value

 
End Sub
  • Now click twice on the clear button and add the following code:
Private Sub ClearButton_Click()

 
Call UserForm_Initialize

 
End Sub
  • Now click on the cancel button twice and add the following code:
Private Sub CancelButton_Click()

 
Unload Me

 
End Sub

Test the Userform

Now close Visual Basic Editor and now open your excel sheet. It will look like this:
 

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Userforms and Range

You can set different ranges in your userform according to your requirement. Here we will set ranges on color. Follow the steps given below to insert the range.

  • Open Visual Basic Editor.
  • Click and insert a new userform. Now your screen will look like this:
     

  • Add the label, as well as the RefEdit control and command buttons. The output should look like the Userform image you saw before when you're done. Create a RefEdit control, for example, by selecting RefEdit from the Toolbox. Then, on the Userform, drag a RefEdit control.
  • The names and captions of the controls may be changed. In the Excel VBA code, names are utilized. The captions that display on your screen are called captions. It is recommended that the names of the controls be changed, but this is not essential in this case since there are just a few controls. Click View, Properties Window, and then click on each control to edit the caption of the Userform, label, and command buttons.
  • To present/show a userform, place a command button on the worksheet and write the following code:
Private Sub CommandButton1_Click()

 
UserForm1.Show

 
End Sub
  • Now we will make a sub userform_initialize for that open Visual Basic Editor.
  • Now click on userform1 and open view code, select initialize from the right list and userform from the left list and write the following code:
Private Sub UserForm_Initialize()

 
Sheet1.Cells.Font.Color = vbBlack
UserForm1.RefEdit1.Text = Selection.Address

 
End Sub
  • Click twice on userform1, then on the go button and after that, add the following code:
Private Sub CommandButton1_Click()

 
Dim addr As String, rng, cell As a range, minimum As Double

 
addr = RefEdit1.Value
Set rng = Range(addr)
minimum = WorksheetFunction.Min(rng)

 
For Each cell In rng
    If cell.Value = minimum Then cell.Font.Color = vbRed
Next cell

 
End Sub
  • Click twice on the excel button and then write the following code:
Private Sub CommandButton2_Click()

 
Unload Me

 
End Sub
  • Test the userform.

Various Programs using Userform

Here we will create different programs and projects using userform from scratch step by step.

Progress Indicator

The progress indicator is a bar that will show the current progress. Here we will see the code to create progress indicators in excel VBA.

  • Open Visual Basic Editor.
  • Click and insert a new userform. Now your screen will be looking like this:
     

  • Add the frame control to the mix. You can do so by clicking on Frame from the Toolbox. Then, on the Userform, drag a frame control. This frame control's attributes need to be changed. Right-click the frame control and choose Properties from the drop-down menu. Set the Width to 204 and Height to 204 in the Caption field.
  • Insert the first label control inside the Frame control. Click the label control with the right mouse button, then choose Properties. Change the name to Bar, the BackColor to Highlight, the Caption field to empty, and the Height and Width to 20 and 10, respectively.
  • Add a second label control above the Frame control and position it there. Right-click the label control, then choose Properties from the drop-down menu. Change the caption to '0 percent Completed' and change the name to Text.
  • To present/show a userform, place a command button on the worksheet and write the following code.
Private Sub CommandButton1_Click()

 
UserForm1.Show

 
End Sub
  • Now open Visual Basic Editor.
  • Select userform1 from the project explorer.
  • Choose activate from the right list and userform from the left list and add the following code:
Private Sub UserForm_Activate()

 
code

 
End Sub
  • Put the code in a module with the same name as the sub-named code (In the Visual Basic Editor, click Insert, Module). This is only an illustration. When you wish to utilize this progress indicator in your macro, here is the spot to put your own code. The following is the code.
Sub code()

 
Dim I As Integer, j As Integer, pctCompl As Single

 
Sheet1.Cells.Clear

 
For i = 1 To 100
    For j = 1 To 1000
        Cells(i, 1).Value = j
    Next j
    pctCompl = i
    progress pctCompl
Next i

 
End Sub
  • We will add another progress named sub, and its code will look like this:
Sub progress(pctCompl As Single)

 
UserForm1.Text.Caption = pctCompl & "% Completed"
UserForm1.Bar.Width = pctCompl * 2

 
DoEvents

 
End Sub
  • Now leave the Visual Basic Editor. The result will look like this on the excel sheet.
     

Currency Converter

Here we will write a program that acts as a currency converter. Follow the steps given below to make a currency converter.

  • Open Visual Basic Editor.
  • Click and insert a new userform. Now your screen will look like this:
     

  • Labels, list boxes (first on the left, second on the right), text boxes (first on the left, second on the right), and a command button should all be included. When you're done, the output should look like the Userform image you saw before. Create a list box control, for example, by selecting ListBox from the Toolbox. After that, drag a list box onto the Userform.
  • The names and captions of the controls may be changed. In the Excel VBA code, names are utilized. The captions that display on your screen are called captions. It is recommended that the names of the controls be changed, but this is not essential in this case since there are just a few controls. Click View, Properties Window, and then click on each control to edit the caption of the Userform, command button, and labels.
  • To present/show a userform, place a command button on the worksheet and write the following code.
Private Sub CommandButton1_Click()

 
UserForm1.Show

 
End Sub
  • Now we will create a userform initialize and, for that, open Visual Basic Editor.
  • Click on userform one and select initialize from the right lit and userform from the left list. After that, write the following code:
Private Sub UserForm_Initialize()

 
With ListBox1
    .AddItem "Euro"
    .AddItem "Us Dollar"
    .AddItem "British Pound"
End With

 
With ListBox2
    .AddItem "Euro"
    .AddItem "Us Dollar"
    .AddItem "British Pound"
End With

 
ListBox1.ListIndex = 1
ListBox2.ListIndex = 0

 
TextBox1.Value = 1
TextBox2.Value = 0.722152

 
End Sub
  • Click twice on the userform and then twice on the go button and add the following lines to it.
Private Sub CommandButton1_Click()

 
Dim rates(0 To 2, 0 To 2) As Double, I As Integer, j As Integer

 
rates(0, 0) = 1
rates(0, 1) = 1.38475
rates(0, 2) = 0.87452

 
rates(1, 0) = 0.722152
rates(1, 1) = 1
rates(1, 2) = 0.63161

 
rates(2, 0) = 1.143484
rates(2, 1) = 1.583255
rates(2, 2) = 1

 
For i = 0 To 2
    For j = 0 To 2
        If ListBox1.ListIndex = i And ListBox2.ListIndex = j Then TextBox2.Value = TextBox1.Value * rates(i, j)
    Next j
Next i

 
End Sub
  • Now close the Visual Basic Editor, and after that, your excel sheet will look like this.
     

Interactive Userform

Here we will create an interactive userform that will take some input to perform operations according to the user and gives the corresponding result.

  • Open Visual Basic Editor.
  • Click and insert a new userform. Now your screen will be looking like this.
     

  • Labels, text boxes (first at the top, second below the first, etc.), and command buttons should all be included. When you're done, the output should look like the Userform image you saw before. Create a text box control, for example, by selecting TextBox from the Toolbox. After that, drag a text box onto the Userform.
  • The names and captions of the controls may be changed. In the Excel VBA code, names are utilized. The captions that display on your screen are called captions. It is recommended that the names of the controls be changed, but this is not essential in this case since there are just a few controls. Click View, Properties Window, and then click on each control to alter the caption of the labels, text fields, and command buttons.
  • To present/show a userform, place a command button on the worksheet and write the following code.
Private Sub CommandButton1_Click()

 
UserForm1.Show

 
End Sub
  • Open Visual Basic Editor and click on userform1 after selecting. After selecting, choose to initialize from the right list and userform from the left list and write the following code:
Private Sub UserForm_Initialize()

 
TextBox1.SetFocus

 
End Sub
  • Right-click UserForm1 in Project Explorer and choose View Code. Select TextBox1 from the drop-down menu on the left. From the right drop-down menu, choose Change. Add the following line to your code:
Private Sub TextBox1_Change()

 
GetData

 
End Sub
  • Click twice on userform1 and edit the command. After that, add the following code:
Private Sub CommandButton1_Click()

 
EditAdd

 
End Sub
  • Click twice on the clear command and after that, add the following code:
Private Sub CommandButton2_Click()

 
ClearForm

 
End Sub
  • Click twice on the close command button, and after that, add the following lines of code:
Private Sub CommandButton3_Click()

 
Unload Me

 
End Sub
  • Declare three variables of type Integer and one of type Boolean first. In the General Declarations section, declare the variables (at the top of the module). You'll only have to define the variables once, and you'll be able to utilize them across several subs.
Dim id As Integer, j As Integer, i As Integer, flag As Boolean
  • Now add the GetData Sub
Sub GetData()

 
If IsNumeric(UserForm1.TextBox1.Value) Then
    flag = False
    i = 0
    id = UserForm1.TextBox1.Value

 
    Do While Cells(i + 1, 1).Value <> ""

 
        If Cells(i + 1, 1).Value = id Then
            flag = True
            For j = 2 To 3
                UserForm1.Controls("TextBox" & j).Value = Cells(i + 1, j).Value
            Next j
        End If

 
        i = i + 1

 
    Loop

 
    If flag = False Then
        For j = 2 To 3
            UserForm1.Controls("TextBox" & j).Value = ""
        Next j
    End If

 
Else
    ClearForm
End If

 
End Sub
  • Now we will add clearform sub
Sub ClearForm()

 
For j = 1 To 3
    UserForm1.Controls("TextBox" & j).Value = ""
Next j

 
End Sub
  • At last, we will add the EditAdd sub:
Sub EditAdd()

 
Dim emptyRow As Long

 
If UserForm1.TextBox1.Value <> "" Then
    flag = False
    i = 0
    id = UserForm1.TextBox1.Value
    emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

 
    Do While Cells(i + 1, 1).Value <> ""

 
        If Cells(i + 1, 1).Value = id Then
            flag = True
            For j = 2 To 3
                Cells(i + 1, j).Value = UserForm1.Controls("TextBox" & j).Value
            Next j
        End If

 
        i = i + 1

 
    Loop

 
    If flag = False Then
        For j = 1 To 3
            Cells(emptyRow, j).Value = UserForm1.Controls("TextBox" & j).Value
        Next j
    End If

 
End If

 
End Sub
  • Now close the Visual Basic Editor, and your excel sheet will look like this:
     

FAQs

  1. Name some of the data types available in the VBA.
    Some of the data types available in VBA are boolean, bytes, currency, date, variant, long, single, and user-defined data types.
     
  2. What do you mean by VBA?
    VBA means Visual Basic for Application. It is the programming language used for almost all operations in excel.
     
  3. What do you understand by the object in VBA?
    As VBA is an object-oriented programming language, the object refers to the worksheet referred to in the code.
     
  4. Can we write macro in the code window?
    Yes, we can write the macro in the code window.

Key Takeaways

In this article, we have extensively discussed Userforms in Excel VBA. Along with that, we have also discussed ranges in userforms and different projects we can make using userforms with their codes and pictorial representation, and at last, we have discussed an interactive userform with its step-by-step explanation.

We hope that this blog has helped you enhance your knowledge of excel. If you are interested in learning about ActiveX controls, you must refer to this blog. Here, we have discussed  Different ActiveX Controls like a list box, combo box, scroll bar, option buttons, command buttons, and many more from scratch and also learned how to control their working with writing code in VBA. If you would like to learn more, check out our articles on Code studio. Do upvote our blog to help other ninjas grow.

 “Happy Coding!”

Previous article
ActiveX Controls
Next article
Web Scraping with VBA
Live masterclass