Post
Ask Question

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

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()  
  3.   
  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.