Today we will read a program in Excel VBA on this page that creates an interactive userform.

Whenever you enter a value in the ID text box, Excel VBA loads the corresponding record. When you click the edit / add button, Excel edits the record on the VBA sheet or adds the record when the ID is not yet present. The clear button clears all text boxes. The Close button turns the userform.

To create this userform, follow these steps.

Open the Visual Basic Editor. If Project Explorer is not visible, then View, Project Explorer -> Insert, click, click. If the toolbox does not appear automatically, click View, Toolbox.

Add labels, text boxes (first at the top, second at the bottom and second) and command buttons. Once this is completed, the result should correspond to the user’s image shown earlier. For example, create a text box control by clicking on the text box from the toolbox. After this, you can drag a text box to the userform.

You can change the captions of names and controls. Names are used in Excel VBA code. Captions are those that appear on your screen. It is good practice to change the names of controls, but this is not necessary here because in this example we have only a few controls. To change the caption of labels, text boxes and command buttons, click View, Properties window, and click each control.

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

Private Sub CommandButton1_Click()

UserForm1.Show

End Sub

Now we are going to create a sub-user. When you use the show method for Userform, this sub will be executed automatically.

Open the Visual Basic Editor -> In Project Explorer, right click on UserForm1 and then choose Userform from the View Code -> Left drop-down list. Choose Start from the right drop-down list -> Add the following code line:

Private Sub UserForm_Initialize()

TextBox1.SetFocus

End Sub

We have now created the first part of Userform. Although it already looks neat, nothing will happen when we enter a value in the ID text box or when we click on one of the command buttons.

In Project Explorer, right click on UserForm1 and then choose View -> TextBox1 from the left drop-down list. Choose Changes from the right drop-down list -> Add the following code line:

Private Sub TextBox1_Change()

GetData

End Sub

In Project Explorer, double click on UserForm1 -> Double click on Edit / Add command button -> Add the following code line:

Private Sub CommandButton1_Click()

EditAdd

End Sub

Double click on the Clear command button ->  Add the following code line:

Private Sub CommandButton2_Click()

ClearForm

End Sub

Double click on the Close command button -> Paste the following code line:

Private Sub CommandButton3_Click()

Unload Me

End Sub

Time to create a sub. You can go through our function and sub chapters to know more about subs. If you are in a hurry, just put the following sub modules in one module (in the Visual Basic Editor, click Insert, Module).

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

Sub ClearForm()

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

End 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