Create Manage Database in Excel with User Form

We can create and manage a database in Excel with a user-form quickly and easily. Earlier we had demonstrated how to create a user-form and an advanced user-form.

Watch the training video below before studying the VBA or macro code:

 

Watch this video on YouTube.

First we define the global variables right at the top of all modules:

Dim blnNew As Boolean
Dim totRows As Long, i As Long

Code for the close button:

Private Sub cmdClose_Click()
If cmdClose.Caption = “Close” Then
Unload Me
End If
If cmdClose.Caption = “Cancel” Then
cmdClose.Caption = “Close”
txtItemNo.Text = “”
txtDescription.Text = “”
txtUnitPrice.Text = “”
txtQty.Text = “”
txtSupplier.Text = “”
cmdNew.Enabled = True
End If
End Sub
VBA code for the Delete button:
Private Sub cmdDelete_Click()
totRows = Worksheets(“Data”).Range(“A1”).CurrentRegion.Rows.Count
Dim strDel As String
strDel = MsgBox(“Sure you want to delete the data?”, vbYesNo, “Delete”)
If strDel = vbYes Then
For i = 2 To totRows
If Trim(Worksheets(“Data”).Cells(i, 1)) = Trim(ComboBox1.Text) Then
Worksheets(“Data”).Range(i & “:” & i).DeletetxtItemNo.Text = “”
txtDescription.Text = “”
txtUnitPrice.Text = “”
txtQty.Text = “”
txtSupplier.Text = “”Call comboboxFill
Exit For
End If
Next i
If Trim(ComboBox1.Text) = “” Then
cmdSave.Enabled = False
cmdDelete.Enabled = False
Else
cmdSave.Enabled = True
cmdDelete.Enabled = True
End If
End If
End Sub
VBA code for the New button:
Private Sub cmdNew_Click()
blnNew = True
txtItemNo.Text = “”
txtDescription.Text = “”
txtUnitPrice.Text = “”
txtQty.Text = “”
txtSupplier.Text = “”
txtItemNo.SetFocus
cmdClose.Caption = “Cancel”
cmdNew.Enabled = False
cmdDelete.Enabled = False
cmdSave.Enabled = True
End Sub
Macro code for the Save Button:
Private Sub cmdSave_Click()
If txtItemNo.Text = “” Then
MsgBox “Enter an Item Number”, vbCritical, “Save”
txtItemNo.SetFocus
Exit Sub
End If
Call pSave
End Sub
VBA code for the pSave procedure:
Private Sub pSave()
If blnNew = True Then
totRows = Worksheets(“Data”).Range(“A1”).CurrentRegion.Rows.Count
With Worksheets(“Data”).Range(“A1”)
.Offset(totRows, 0) = txtItemNo.Text
.Offset(totRows, 1) = txtDescription.Text
.Offset(totRows, 2) = txtUnitPrice.Text
.Offset(totRows, 3) = txtQty.Text
.Offset(totRows, 4) = txtSupplier.Text
End With
Call comboboxFill
Else
totRows = Worksheets(“Data”).Range(“A1”).CurrentRegion.Rows.Count
For i = 2 To totRows
    If Trim(Worksheets(“Data”).Cells(i, 1)) = Trim(ComboBox1.Text) Then
Worksheets(“Data”).Cells(i, 1) = txtItemNo.Text
Worksheets(“Data”).Cells(i, 2).Value = txtDescription.Text
Worksheets(“Data”).Cells(i, 3).Value = txtUnitPrice.Text
Worksheets(“Data”).Cells(i, 4).Value = txtQty.Text
Worksheets(“Data”).Cells(i, 5).Value = txtSupplier.Text
txtItemNo = “”
txtDescription = “”
txtUnitPrice = “”
txtQty = “”
txtSupplier = “”
Exit For
End If
Next i
End If
cmdSave.Enabled = True
cmdDelete.Enabled = False
cmdNew.Enabled = True
cmdClose.Caption = “Close”
blnNew = False
End Sub
VBA cod for the Search button:
Private Sub cmdSearch_Click()
blnNew = False
txtItemNo = “”
txtDescription = “”
txtUnitPrice = “”
txtQty = “”
txtSupplier = “”
totRows = Worksheets(“Data”).Range(“A1”).CurrentRegion.Rows.Count
For i = 2 To totRows
    If Trim(Worksheets(“Data”).Cells(i, 1)) = Trim(ComboBox1.Text) Then
txtItemNo.Text = Worksheets(“Data”).Cells(i, 1)
txtDescription.Text = Worksheets(“Data”).Cells(i, 2).Value
txtUnitPrice.Text = Worksheets(“Data”).Cells(i, 3).Value
txtQty.Text = Worksheets(“Data”).Cells(i, 4).Value
txtSupplier.Text = Worksheets(“Data”).Cells(i, 5).ValueExit For
End IfNext i
If txtItemNo.Text = “” Then
MsgBox “Select an Item Number”
Else
cmdSave.Enabled = True
cmdDelete.Enabled = True
End If
End Sub
Macro code for the ComboBoxFill procedure:
Private Sub comboboxFill()
ComboBox1.Clear
totRows = Worksheets(“Data”).Range(“A1”).CurrentRegion.Rows.Count
For i = 2 To totRows
ComboBox1.AddItem Worksheets(“Data”).Cells(i, 1).Value
Next i
End Sub
Macro code for the user-form initialization:
Private Sub UserForm_Initialize()
Call comboboxFill
cmdSave.Enabled = False
cmdDelete.Enabled = False
End Sub
Further reading:

6 thoughts on “Create Manage Database in Excel with User Form

  1. Adil Rangooni

    Hello Sir I see all your videos and I saw your new video where you have shown how to make database entry with userform.
    But I have a question….what will happen if we make two entry with the same item number….how can we avoid that mistake…or can the userform auto generate the unused item number…Thank You

    Reply
  2. Jon

    Hi, thanks for your video, its really usefull, but i have one problem, i couldn’t understand about Psave, could you please more explain about it, even i couldn’t find it.

    thanks
    jon

    Reply
  3. Terry

    I keep getting the following error:
    “Run-time error ‘9’ Subscript out of range”.

    Just curious if any of your other subscribers have experienced the same. I copied the code line for line, check everything multiple times. I cannot find where I mistyped something.
    BTW, your videos are great.

    Reply
  4. Neelakanth

    sir i am neelakanth i have sent below code not working and not transfer the data next data sheets so plz check and confirm me sir

    Dim bnlNew As Boolean
    Dim totRows As Long, i As Long

    Private Sub cmdClose_Click()
    If cmdClose.Caption = “Close” Then
    Unload Me
    End If

    If cmdClose.Caption = “Cancel” Then
    cmdClose.Caption = “Close”
    txtTanNo.Text = “”
    txtName.Text = “”
    txtAdd.Text = “”
    txtPanNo.Text = “”
    txtAY.Text = “”
    txtFY.Text = “”
    txtNameofperson.Text = “”
    txtFatherName.Text = “”
    txtDesignation.Text = “”
    txtPlace.Text = “”
    txtDate.Text = “”
    txtAdd1.Text = “”
    txtCity.Text = “”
    txtPinecode.Text = “”

    cmdNew.Enabled = True
    End If

    End Sub

    Private Sub cmdDelete_Click()
    totRows = Worksheets(“Data”).Range(“A1”).CurrentRegion.Rows.Count
    Dim strDel As String
    strDel = MsgBox(“Sure you want to delete the Backup?”, vbYesNo, “Delete”)
    If strDel = vbYes Then
    For i = 2 To totRows
    If Trim(Worksheets(“Data”).Cells(i, 1)) = Trim(ComboBox1.Text) Then
    Worksheets(“Data”).Range(i & “:” & i).Delete

    txtTanNo.Text = “”
    txtName.Text = “”
    txtAdd.Text = “”
    txtPanNo.Text = “”
    txtAY.Text = “”
    txtFY.Text = “”
    txtNameofperson.Text = “”
    txtFatherName.Text = “”
    txtDesignation.Text = “”
    txtPlace.Text = “”
    txtDate.Text = “”
    txtAdd1.Text = “”
    txtCity.Text = “”
    txtPinecode.Text = “”

    Call ComboBoxFill
    Exit For
    End If
    Next i

    If Trim(CoboBox1.Text) = “” Then
    cmdSave.Enabled = False
    cmdDelete.Enabled = False
    Else
    cmdSave.Enabled = True
    cmdDelete.Enabled = True
    End If

    End If

    End Sub

    Private Sub cmdNew_Click()
    blnNew = True
    txtTanNo.Text = “”
    txtName.Text = “”
    txtAdd.Text = “”
    txtPanNo.Text = “”
    txtAY.Text = “”
    txtFY.Text = “”
    txtNameofperson.Text = “”
    txtFatherName.Text = “”
    txtDesignation.Text = “”
    txtPlace.Text = “”
    txtDate.Text = “”
    txtAdd1.Text = “”
    txtCity.Text = “”
    txtPinecode.Text = “”

    txtTanNo.SetFocus
    cmdClose.Caption = “Cancel”
    cmdNew.Enabled = False
    cmdDelete.Enabled = False
    cmdSave.Enabled = True

    End Sub

    Private Sub cmdSave_Click()

    If txtTanNo.Text = “” Then
    MsgBox “Enter an Tan Number”, vbCritical, “Save”
    txtTanNo.SetFocus
    Exit Sub

    End If
    Call pSave

    End Sub

    Private Sub pSave()
    If blnNew = True Then
    totRows = Worksheets(“Data”).Range(“A1”).CurrentRegion.Rows.Count
    With Worksheets(“Data”).Range(“A1”)
    .Offset(totRows, 0) = txtTanNo.Text
    .Offset(totRows, 1) = txtName.Text
    .Offset(totRows, 2) = txtAdd.Text
    .Offset(totRows, 3) = txtPanNo.Text
    .Offset(totRows, 4) = txtAY.Text
    .Offset(totRows, 5) = txtFY.Text
    .Offset(totRows, 6) = txtNameofperson.Text
    .Offset(totRows, 7) = txtFatherName.Text
    .Offset(totRows, 8) = txtDesignation.Text
    .Offset(totRows, 9) = txtPlace.Text
    .Offset(totRows, 10) = txtDate.Text
    .Offset(totRows, 11) = txtAdd1.Text
    .Offset(totRows, 12) = txtCity.Text
    .Offset(totRows, 13) = txtPinecode.Text
    End With
    Call ComboBoxFill

    Else
    totRows = Worksheets(“Data”).Range(“A1”).CurrentRegion.Rows.Count
    For i = 2 To totRows
    If Trim(Worksheets(“Data”).Cells(i, 1)) = Trim(ComboBox1.Text) Then
    Worksheets(“Data”).Cells(i, 1) = txtTanNo.Text
    Worksheets(“Data”).Cells(i, 2).Value = txtName.Text
    Worksheets(“Data”).Cells(i, 3).Value = txtAdd.Text
    Worksheets(“Data”).Cells(i, 4).Value = txtPanNo.Text
    Worksheets(“Data”).Cells(i, 5).Value = txtAY.Text
    Worksheets(“Data”).Cells(i, 6).Value = txtFY.Text
    Worksheets(“Data”).Cells(i, 7).Value = txtNameofperson.Text
    Worksheets(“Data”).Cells(i, 8).Value = txtFatherName.Text
    Worksheets(“Data”).Cells(i, 9).Value = txtDesignation.Text
    Worksheets(“Data”).Cells(i, 10).Value = txtPlace.Text
    Worksheets(“Data”).Cells(i, 11).Value = txtDate.Text
    Worksheets(“Data”).Cells(i, 12).Value = txtAdd1.Text
    Worksheets(“Data”).Cells(i, 13).Value = txtCity.Text
    Worksheets(“Data”).Cells(i, 14).Value = txtPinecode.Text
    txtTanNo.Text = “”
    txtName.Text = “”
    txtAdd.Text = “”
    txtPanNo.Text = “”
    txtAY.Text = “”
    txtFY.Text = “”
    txtNameofperson.Text = “”
    txtFatherName.Text = “”
    txtDesignation.Text = “”
    txtPlace.Text = “”
    txtDate.Text = “”
    txtAdd1.Text = “”
    txtCity.Text = “”
    txtPinecode.Text = “”
    Exit For
    End If
    Next i

    End If

    cmdSave.Enabled = True
    cmdDelete.Enabled = False
    cmdNew.Enabled = True
    cmdClose.Caption = “Close”

    blnNew = False

    End Sub

    Private Sub cmdSearch_Click()
    blnNew = False
    txtTanNo.Text = “”
    txtName.Text = “”
    txtAdd.Text = “”
    txtPanNo.Text = “”
    txtAY.Text = “”
    txtFY.Text = “”
    txtNameofperson.Text = “”
    txtFatherName.Text = “”
    txtDesignation.Text = “”
    txtPlace.Text = “”
    txtDate.Text = “”
    txtAdd1.Text = “”
    txtCity.Text = “”
    txtPinecode.Text = “”

    totRows = Worksheets(“Data”).Range(“A1”).CurrentRegion.Rows.Count

    For i = 2 To totRows

    If Trim(Worksheets(“Data”).Cells(i, 1)) = Trim(ComboBox1.Text) Then
    txtTanNo.Text = Worksheets(“Data”).Cells(i, 1)
    txtName.Text = Worksheets(“Data”).Cells(i, 2).Value
    txtAdd.Text = Worksheets(“Data”).Cells(i, 3).Value
    txtPanNo.Text = Worksheets(“Data”).Cells(i, 4).Value
    txtAY.Text = Worksheets(“Data”).Cells(i, 5).Value
    txtFY.Text = Worksheets(“Data”).Cells(i, 6).Value
    txtNameofperson.Text = Worksheets(“Data”).Cells(i, 7).Value
    txtFatherName.Text = Worksheets(“Data”).Cells(i, 8).Value
    txtDesignation.Text = Worksheets(“Data”).Cells(i, 9).Value
    txtPlace.Text = Worksheets(“Data”).Cells(i, 10).Value
    txtDate.Text = Worksheets(“Data”).Cells(i, 11).Value
    txtAdd1.Text = Worksheets(“Data”).Cells(i, 12).Value
    txtCity.Text = Worksheets(“Data”).Cells(i, 13).Value
    txtPinecode.Text = Worksheets(“Data”).Cells(i, 14).Value

    Exit For
    End If
    Next i

    If txtTanNo.Text = “” Then
    MsgBox “Select an Tan Number”
    Else
    cmdSave.Enabled = True
    cmdDelete.Enabled = True
    End If

    End Sub

    Private Sub ComboBoxFill()
    ComboBox1.Clear
    totRows = Worksheets(“Data”).Range(“A1”).CurrentRegion.Rows.Count
    For i = 2 To totRows
    ComboBox1.AddItem Worksheets(“Data”).Cells(i, 1).Value
    Next i
    End Sub

    Private Sub UserForm_Initialize()

    Call ComboBoxFill

    cmdSave.Enabled = False
    cmdDelete.Enabled = False

    End Sub

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *