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

form

In the image I have created four forms:

Form 1 VBA Code:

  1. Private Sub CommandButton1_Click()
  2. UserForm2.Show
  3. End Sub
  4. Private Sub CommandButton2_Click()
  5. UserForm3.Show
  6. End Sub
In the above code I have shown the form according to the button clicked.

Form2 VBA Code:
  1. Private Sub CommandButton1_Click()
  2. Dim LastRowsAs Long
  3. With ActiveSheet
  4. LastRows = .Cells(.Rows.Count, "A").End(xlUp).Row
  5. End With
  6. Cells(LastRows + 1, 1).Value = Me.TextBox1.Text
  7. Cells(LastRows + 1, 2).Value = Me.TextBox2.Text
  8. Cells(LastRows + 1, 3).Value = Me.TextBox3.Text
  9. Cells(LastRows + 1, 4).Value = Me.TextBox4.Text
  10. Cells(LastRows + 1, 5).Value = Me.TextBox5.Text
  11. Cells(LastRows + 1, 6).Value = Me.TextBox6.Text
  12. Cells(LastRows + 1, 7).Value = Me.TextBox7.Text
  13. 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
  14. Cells(LastRows + 1, 9).Value = Cells(LastRows + 1, 8).Value / 5
  15. MsgBox ("Data Sucussfull Added")
  16. UserForm1.Hide
  17. End Sub
The above code is allowed to add the data to the active excel sheet.
  1. With ActiveSheet
  2. LastRows = .Cells(.Rows.Count, "A").End(xlUp).Row
  3. 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:
  1. Private Sub CommandButton1_Click()
  2. Dim i As Long
  3. Dim dsrc As Range
  4. Set dsrc = searchs()
  5. If Not dsrc Is Nothing Then
  6. i = dsrc.Row
  7. Me.Label2.Visible = True
  8. Me.Label3.Visible = True
  9. Me.Label4.Visible = True
  10. Me.TextBox2.Visible = True
  11. Me.TextBox3.Visible = True
  12. Me.TextBox4.Visible = True
  13. Me.TextBox2.Value = Cells(i, 2).Value
  14. Me.TextBox3.Value = Cells(i, 8).Value
  15. Me.TextBox4.Value = Cells(i, 9).Value
  16. Me.CommandButton2.Visible = True
  17. Else
  18. MsgBox ("Data not Found!!!")
  19. Me.TextBox1.Value = ""
  20. End If
  21. End Sub
  22. Function searchs() As Range
  23. With ActiveSheet.Range("A:A")
  24. Set searchs = .Find(What:=Me.TextBox1.Value, _
  25. after:=.Cells(1), _
  26. LookIn:=xlValues, _
  27. LookAt:=xlWhole, _
  28. SearchOrder:=xlByRows, _
  29. SearchDirection:=xlPrevious)
  30. End With
  31. End Function
  32. Private Sub CommandButton2_Click()
  33. UserForm4.Show
  34. 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:
  1. Dim i As Long
  2. Private Sub CommandButton1_Click()
  4. If (i <> 0) Then
  5. Cells(i, 1).Value = Me.TextBox8.Text
  6. Cells(i, 2).Value = Me.TextBox2.Text
  7. Cells(i, 3).Value = Me.TextBox3.Text
  8. Cells(i, 4).Value = Me.TextBox4.Text
  9. Cells(i, 5).Value = Me.TextBox5.Text
  10. Cells(i, 6).Value = Me.TextBox6.Text
  11. Cells(i, 7).Value = Me.TextBox7.Text
  12. Cells(i, 8).Value = Cells(i, 3).Value + Cells(i, 4).Value + Cells(i, 5).Value + Cells(i, 6).Value + Cells(i, 7).Value
  13. Cells(i, 9).Value = Cells(i, 8).Value / 5
  14. MsgBox ("Data Sucussfull Updated")
  15. End If
  16. End Sub
  17. Private Sub UserForm_Activate()
  18. Dim dsrc As Range
  19. i = 0
  20. Set dsrc = searchs(UserForm3.TextBox1.Value)
  21. If Not dsrc Is Nothing Then
  22. i = dsrc.Row
  23. Me.TextBox8.Value = Cells(i, 1).Value
  24. Me.TextBox2.Value = Cells(i, 2).Value
  25. Me.TextBox3.Value = Cells(i, 3).Value
  26. Me.TextBox4.Value = Cells(i, 4).Value
  27. Me.TextBox5.Value = Cells(i, 5).Value
  28. Me.TextBox6.Value = Cells(i, 6).Value
  29. Me.TextBox7.Value = Cells(i, 7).Value
  30. End If
  31. End Sub
  32. Function searchs(ByRefval As String) As Range
  33. With ActiveSheet.Range("A:A")
  34. Set searchs = .Find(What:=val, _
  35. after:=.Cells(1), _
  36. LookIn:=xlValues, _
  37. LookAt:=xlWhole, _
  38. SearchOrder:=xlByRows, _
  39. SearchDirection:=xlPrevious)
  40. End With
  41. End Function
Form 4 code is allow to update the existing data. If I run the project I get the following output.

output

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

Table

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.

run

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.