How to create a dynamic data entry form in excel?

for add:

 Private Sub CommandButton1_Click()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Database")

Dim Last_Row As Long
Last_Row = Application.WorksheetFunction.CountA(sh.Range("A:A"))
'========================= validation ==========================
If Me.TextBox1.Value = "" Then
MsgBox "Please enter the Date", vbCritical
Exit Sub
End If

If Me.TextBox2.Value = "" Then
MsgBox "Please enter the Tracking No", vbCritical
Exit Sub
End If

If Me.TextBox3.Value = "" Then
MsgBox "Please enter the Vehicle NO", vbCritical
Exit Sub
End If

If Me.TextBox4.Value = "" Then
MsgBox "Please enter the Address", vbCritical
Exit Sub
End If

'========================= validation ==========================
sh.Range("A" & Last_Row + 1).Value = "=Row()-1"
sh.Range("B" & Last_Row + 1).Value = Me.TextBox1.Value
sh.Range("C" & Last_Row + 1).Value = Me.TextBox2.Value
sh.Range("D" & Last_Row + 1).Value = Me.TextBox3.Value
sh.Range("E" & Last_Row + 1).Value = Me.TextBox4.Value
sh.Range("F" & Last_Row + 1).Value = Now

Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""

Call refresh_data

End Sub

for UPDATE

Private Sub CommandButton2_Click()

If Me.TextBox5.Value = "" Then
MsgBox "Select the record to update"
    Exit Sub
End If
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Database")

Dim Selected_Row As Long
Selected_Row = Application.WorksheetFunction.Match(CLng(Me.TextBox5.Value), sh.Range("A:A"), 0)

'========================= validation ==========================
If Me.TextBox1.Value = "" Then
MsgBox "Please enter the Date", vbCritical
Exit Sub
End If

If Me.TextBox2.Value = "" Then
MsgBox "Please enter the Tracking No", vbCritical
Exit Sub
End If

If Me.TextBox3.Value = "" Then
MsgBox "Please enter the Vehicle NO", vbCritical
Exit Sub
End If

If Me.TextBox4.Value = "" Then
MsgBox "Please enter the Address", vbCritical
Exit Sub
End If




'========================= validation ==========================
sh.Range("B" & Selected_Row).Value = Me.TextBox1.Value
sh.Range("C" & Selected_Row).Value = Me.TextBox2.Value
sh.Range("D" & Selected_Row).Value = Me.TextBox3.Value
sh.Range("E" & Selected_Row).Value = Me.TextBox4.Value
sh.Range("F" & Selected_Row).Value = Now

Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""

Call refresh_data

End Sub

FOR DELETE 

Private Sub CommandButton3_Click()

If Me.TextBox5.Value = "" Then
MsgBox "Select the record to update"
    Exit Sub
End If
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Database")

Dim Selected_Row As Long
Selected_Row = Application.WorksheetFunction.Match(CLng(Me.TextBox5.Value), sh.Range("A:A"), 0)

sh.Range("A" & Selected_Row).EntireRow.Delete

Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""
Call refresh_data

End Sub

FOR SAVE

Private Sub CommandButton4_Click()
ThisWorkbook.Save
MsgBox "Data save successfully"
End Sub

FOR LIST BOX

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

Me.TextBox5.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 0)
Me.TextBox1.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 1)
Me.TextBox2.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 2)
Me.TextBox3.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 3)
Me.TextBox4.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 4)

Over all code:

Private Sub CommandButton1_Click()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Database")

Dim Last_Row As Long
Last_Row = Application.WorksheetFunction.CountA(sh.Range("A:A"))
'========================= validation ==========================
If Me.TextBox1.Value = "" Then
MsgBox "Please enter the Date", vbCritical
Exit Sub
End If

If Me.TextBox2.Value = "" Then
MsgBox "Please enter the Tracking No", vbCritical
Exit Sub
End If

If Me.TextBox3.Value = "" Then
MsgBox "Please enter the Vehicle NO", vbCritical
Exit Sub
End If

If Me.TextBox4.Value = "" Then
MsgBox "Please enter the Address", vbCritical
Exit Sub
End If



'========================= validation ==========================
sh.Range("A" & Last_Row + 1).Value = "=Row()-1"
sh.Range("B" & Last_Row + 1).Value = Me.TextBox1.Value
sh.Range("C" & Last_Row + 1).Value = Me.TextBox2.Value
sh.Range("D" & Last_Row + 1).Value = Me.TextBox3.Value
sh.Range("E" & Last_Row + 1).Value = Me.TextBox4.Value
sh.Range("F" & Last_Row + 1).Value = Now

Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""

Call refresh_data




End Sub

Private Sub CommandButton2_Click()

If Me.TextBox5.Value = "" Then
MsgBox "Select the record to update"
    Exit Sub
End If
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Database")

Dim Selected_Row As Long
Selected_Row = Application.WorksheetFunction.Match(CLng(Me.TextBox5.Value), sh.Range("A:A"), 0)

'========================= validation ==========================
If Me.TextBox1.Value = "" Then
MsgBox "Please enter the Date", vbCritical
Exit Sub
End If

If Me.TextBox2.Value = "" Then
MsgBox "Please enter the Tracking No", vbCritical
Exit Sub
End If

If Me.TextBox3.Value = "" Then
MsgBox "Please enter the Vehicle NO", vbCritical
Exit Sub
End If

If Me.TextBox4.Value = "" Then
MsgBox "Please enter the Address", vbCritical
Exit Sub
End If




'========================= validation ==========================
sh.Range("B" & Selected_Row).Value = Me.TextBox1.Value
sh.Range("C" & Selected_Row).Value = Me.TextBox2.Value
sh.Range("D" & Selected_Row).Value = Me.TextBox3.Value
sh.Range("E" & Selected_Row).Value = Me.TextBox4.Value
sh.Range("F" & Selected_Row).Value = Now

Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""

Call refresh_data

End Sub

Private Sub CommandButton3_Click()

If Me.TextBox5.Value = "" Then
MsgBox "Select the record to update"
    Exit Sub
End If
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Database")

Dim Selected_Row As Long
Selected_Row = Application.WorksheetFunction.Match(CLng(Me.TextBox5.Value), sh.Range("A:A"), 0)

sh.Range("A" & Selected_Row).EntireRow.Delete

Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""
Call refresh_data

End Sub

Private Sub CommandButton4_Click()
ThisWorkbook.Save
MsgBox "Data save successfully"
End Sub

Private Sub Label1_Click()

End Sub



Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

Me.TextBox5.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 0)
Me.TextBox1.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 1)
Me.TextBox2.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 2)
Me.TextBox3.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 3)
Me.TextBox4.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 4)


End Sub

Private Sub TextBox5_Change()

End Sub

Private Sub UserForm_Activate()
Call refresh_data

End Sub
Sub refresh_data()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Database")

Dim Last_Row As Long
Last_Row = Application.WorksheetFunction.CountA(sh.Range("A:A"))

With Me.ListBox1
    .ColumnHeads = True
    .ColumnCount = 15
    .ColumnWidths = "30,80,60,90,100,30"
    
    If Last_Row = 1 Then
    .RowSource = "Database!A2:F2"
    Else
    
    .RowSource = "Database!A2:F" & Last_Row
    
End If
End With
End Sub



Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Application.Visible = True
Unload Me

End Sub