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:

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

15 thoughts on “Create Manage Database in Excel with User Form”

  1. 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

  2. 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

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

  4. 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

    1. Thanks a lot for your video sir. Really it’s so useful. Sir based on your video I have developed a small tool for data entry update and it is working fine if I am searching for a number but it is not working if I am searching with Text. Sir, please help me out where I need to change the code to search with “text” and I want to search column “I” (column no. 9). Please sir help me how to do it

      1. I am getting that too. I’ve been debugging it and finding small errors ie. something isn’t capitalized that was before or two End If statements.
        However, now it says that my problem lies within the line t

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

        Any help would be appreciated, on what I could tweak, I am new to VBA and am slowly trying to figure the language out.

      2. Here’s what I found to fix the error.

        For some reason, when I copied and pasted the code, the quotation marks were not true quotation marks. By re-typing the lines of code or at least the quotation marks, it worked.

  5. Hi Dinesh.
    Thanks for giving such good example, it is very useful.
    it is working perfectly.

    Thanks of Lot..!

    Can you please give notification of your upcoming videos on my email.

  6. It works just need to replace “”

    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).Delete

    txtItemNo.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).Value

    Exit For
    End If

    Next 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

    Private Sub txtSupplier_Change()

    End Sub

    Private Sub txtUnitPrice_Change()

    End Sub

    ‘Macro code for the user-form initialization:
    Private Sub UserForm_Initialize()
    Call comboboxFill
    cmdSave.Enabled = False
    cmdDelete.Enabled = False
    End Sub

Leave a Reply

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