Month: December 2015

Create Manage Database in Excel with User Form

Posted on by Dinesh Kumar Takyar

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: