How to make data entries with User-Form, Vlookup, Countif and Dynamic Named Ranges to create databases quickly and easily. Watch the video below:

 

Watch this video on YouTube.

Here’s the complete VBA code:

Module1

Sub showItemForm()
frmItem.Show
frmItem.tb1.SetFocus

End Sub

Module2

Sub showCustomerForm()
frmCustomer.Show
frmCustomer.tb1.SetFocus

End Sub

frmCustomer

Private Sub cmdAddCustomer_Click()
Sheet2.Activate
erow = Sheet2.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row
Cells(erow, 3) = tb1.Value
Cells(erow, 4) = tb2.Value
Cells(erow, 5) = tb3.Value
Cells(erow, 6) = tb4.Value
Cells(erow, 7) = tb5.Value
Cells(erow, 8) = tb6.Value
End Sub

Private Sub cmdClear_Click()

For i = 1 To 6
Me.Controls(“tb” & i) = “”
Next

End Sub

Private Sub CommandButton1_Click()

Unload Me

End Sub

Private Sub tb1_AfterUpdate()
Sheet2.Activate
If WorksheetFunction.CountIf(Sheet2.Range(“C:C”), Me.tb1.Value) = 0 Then
MsgBox “Wish to add this new customer?”, vbOKCancel, “Add Customer”
End If
If vbOK Then
cmdAddCustomer.Visible = True
Me.tb2.SetFocus
‘Exit Sub
End If

If WorksheetFunction.CountIf(Sheet2.Range(“C:C”), Me.tb1.Value) > 0 Then
cmdAddCustomer.Visible = False
MsgBox “Customer already exists! The data is displayed.”

With Me
.tb2 = Application.WorksheetFunction.VLookup((Me.tb1), Sheet2.Range(“customers”), 2, 0)
.tb3 = Application.WorksheetFunction.VLookup((Me.tb1), Sheet2.Range(“customers”), 3, 0)
.tb4 = Application.WorksheetFunction.VLookup((Me.tb1), Sheet2.Range(“customers”), 4, 0)
.tb5 = Application.WorksheetFunction.VLookup((Me.tb1), Sheet2.Range(“customers”), 5, 0)
.tb6 = Application.WorksheetFunction.VLookup((Me.tb1), Sheet2.Range(“customers”), 6, 0)
End With

End If

End Sub

Private Sub UserForm_Initialize()
tb1.SetFocus
End Sub

frmItems

Private Sub cmdAddItem_Click()

Dim erow As Long
Sheet3.Activate
erow = Sheet3.Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).Row
Cells(erow, 4) = tb1.Value
Cells(erow, 5) = tb2.Value
Cells(erow, 6) = Val(tb2) * (Val(tb3) / 100)
Cells(erow, 7) = Cells(erow, 6) / 2
Cells(erow, 8) = Cells(erow, 6) / 2
Cells(erow, 9) = Cells(erow, 5) + Cells(erow, 6)
Cells(erow, 10) = tb4.Value

End Sub

Private Sub cmdClear_Click()
For i = 1 To 4

Me.Controls(“tb” & i) = “”

Next

End Sub

Private Sub CommandButton1_Click()
Unload Me

End Sub

Private Sub tb1_AfterUpdate()
Sheet3.Activate
If WorksheetFunction.CountIf(Sheet3.Range(“D:D”), Me.tb1.Value) = 0 Then
MsgBox “Wish to add this new item?”, vbOKCancel, “Add Item”
End If
If vbOK Then
cmdAddItem.Visible = True
Me.tb2.SetFocus
End If

If WorksheetFunction.CountIf(Sheet3.Range(“D:D”), Me.tb1.Value) > 0 Then
cmdAddItem.Visible = False
MsgBox “Item already exists! The data is displayed.”
With Me
.tb2 = Application.WorksheetFunction.VLookup((Me.tb1), Sheet3.Range(“items”), 2, 0)
.tb3 = Application.WorksheetFunction.VLookup((Me.tb1), Sheet3.Range(“items”), 3, 0)
.tb4 = Application.WorksheetFunction.VLookup((Me.tb1), Sheet3.Range(“items”), 7, 0)
End With
End If
End Sub

 

Private Sub UserForm_Initialize()
tb1.SetFocus
End Sub

Further reading:

Refer to Named Range Excel VBA

Dynamic Named Ranges Excel

Using Offset Property in VBA

Vlookup Excel VBA

How to use Vlookup to get multiple values