Using Form in Excel VBA Script

Excel work book plays an important role in many fields, it allows us to maintain large amounts of data. We can also use Excel as a database. In a lot of programming we will use the Excel sheet as a template to import and export data from the database. Excel is one of the simplest forms of databases and it is easy to use.

VBA script is one of the simplest scripts, it’s like other scripting languages. We do not need a separate tool to create the script, we write it in Excel using the developer’s tool.

In this article I would like to create the simplest form to insert, view and update operating in the existing Excel sheet.

In Excel, go to developers tools and select Visual Basic. A dialog box will appear. In the dialog box choose the current excel and create a form like the below image



In the image I have created four forms:

Form 1 VBA Code:

Private Sub CommandButton1_Click()
UserForm2.Show
End Sub
Private Sub CommandButton2_Click()
UserForm3.Show
End Sub

In the above code I have shown the form according to the button clicked.

Form2 VBA Code:

Private Sub CommandButton1_Click()
Dim LastRowsAs Long
With ActiveSheet
LastRows = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
Cells(LastRows + 1, 1).Value = Me.TextBox1.Text
Cells(LastRows + 1, 2).Value = Me.TextBox2.Text
Cells(LastRows + 1, 3).Value = Me.TextBox3.Text
Cells(LastRows + 1, 4).Value = Me.TextBox4.Text
Cells(LastRows + 1, 5).Value = Me.TextBox5.Text
Cells(LastRows + 1, 6).Value = Me.TextBox6.Text
Cells(LastRows + 1, 7).Value = Me.TextBox7.Text
Cells(LastRows + 1, 8).Value = Cells(LastRows + 1, 3).Value + Cells(LastRows + 1, 4).Value + Cells(LastRows + 1, 5).Value + Cells(LastRows + 1, 6).Value + Cells(LastRows + 1, 7).Value
Cells(LastRows + 1, 9).Value = Cells(LastRows + 1, 8).Value / 5
MsgBox ("Data Sucussfull Added")
UserForm1.Hide
End Sub

The above code is allowed to add the data to the active excel sheet.

With ActiveSheet
LastRows = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

The above line of code allows us to find a number of values entered in the Excel sheet.
After finding the last values of the sheet we will assign the values to the each cell accordingly.

Form 3 VBA Code:

Private Sub CommandButton1_Click()
Dim i As Long
Dim dsrc As Range
Set dsrc = searchs()
If Not dsrc Is Nothing Then
i = dsrc.Row
Me.Label2.Visible = True
Me.Label3.Visible = True
Me.Label4.Visible = True
Me.TextBox2.Visible = True
Me.TextBox3.Visible = True
Me.TextBox4.Visible = True
Me.TextBox2.Value = Cells(i, 2).Value
Me.TextBox3.Value = Cells(i, 8).Value
Me.TextBox4.Value = Cells(i, 9).Value
Me.CommandButton2.Visible = True
Else
MsgBox ("Data not Found!!!")
Me.TextBox1.Value = ""
End If
End Sub
Function searchs() As Range
With ActiveSheet.Range("A:A")
            Set searchs = .Find(What:=Me.TextBox1.Value, _
after:=.Cells(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious)
End With
End Function
Private Sub CommandButton2_Click()
UserForm4.Show
End Sub

Form three code allows us to find the searched student id if found it will display the result else it will show the message box.

Form 4 VBA code:

Dim i As Long
Private Sub CommandButton1_Click()

If (i <> 0) Then
Cells(i, 1).Value = Me.TextBox8.Text
Cells(i, 2).Value = Me.TextBox2.Text
Cells(i, 3).Value = Me.TextBox3.Text
Cells(i, 4).Value = Me.TextBox4.Text
Cells(i, 5).Value = Me.TextBox5.Text
Cells(i, 6).Value = Me.TextBox6.Text
Cells(i, 7).Value = Me.TextBox7.Text
Cells(i, 8).Value = Cells(i, 3).Value + Cells(i, 4).Value + Cells(i, 5).Value + Cells(i, 6).Value + Cells(i, 7).Value
Cells(i, 9).Value = Cells(i, 8).Value / 5
MsgBox ("Data Sucussfull Updated")
End If
End Sub
Private Sub UserForm_Activate()
Dim dsrc As Range
i = 0
Set dsrc = searchs(UserForm3.TextBox1.Value)
If Not dsrc Is Nothing Then
i = dsrc.Row
Me.TextBox8.Value = Cells(i, 1).Value
Me.TextBox2.Value = Cells(i, 2).Value
Me.TextBox3.Value = Cells(i, 3).Value
Me.TextBox4.Value = Cells(i, 4).Value
Me.TextBox5.Value = Cells(i, 5).Value
Me.TextBox6.Value = Cells(i, 6).Value
Me.TextBox7.Value = Cells(i, 7).Value
End If
End Sub
Function searchs(ByRefval As String) As Range
With ActiveSheet.Range("A:A")
            Set searchs = .Find(What:=val, _
after:=.Cells(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious)
End With
End Function

Form 4 code is allow to update the existing data. If I  run the project I get the following output.



The first output shows the sample for of the insertion date. If the data is inserted it will show the message box.



By clicking the view date able to visible the search form. We can search the data using the student id if the data found it shows few data in the search form else it will show message box that the data has not found.



By clicking the edit button we are  able to edit the searched data. If we want, we can change the data as per our need. If the data is edited it will show the message box.

When we are using the excel sheet with huge amounts of data or frequently used Excel, we can use these types of VBA script forms to view the values or edit the values. it will save time and it's easy to use the values.