I have created a userform for warehouse with 8 text boxes with the an "Add Record" button to enter record into the database.
To make sure duplicated records are not being entered (e.g. same item being inputted into system on same date), I want to setup Macro to ensure same combination of "Material Name" (Column B) and "Date" (Column D) won't be entered for more than one time.
I got great help from Perpa but still couldn't make the form work since it shows there is a type mismatch in the "M_Date = Me.Controls("Reg" & 4).Value" , seems I have missed something in the code and would be great if I can find out what is the reason causing the problem, thank you!
To make sure duplicated records are not being entered (e.g. same item being inputted into system on same date), I want to setup Macro to ensure same combination of "Material Name" (Column B) and "Date" (Column D) won't be entered for more than one time.
Option Explicit
'Private variables
Dim cNum As Integer
Dim X As Integer
Private Sub Reg2_Change()
End Sub
Private Sub UserForm_Initialize()
Reg1.List = Sheet4.Range("A2:A10").Value
Reg3.List = Sheet4.Range("L2:L3").Value
End Sub
Private Sub Reg4_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'check date format
With Reg4
If Len(.Text) <> 10 Or _
Mid(.Text, 3, 1) <> "/" Or _
Mid(.Text, 6, 1) <> "/" Then
MsgBox "Date format must be mm/dd/yyyy"
Cancel = True
Exit Sub
End If
End With
If Not IsDate(Reg4) Or Mid(Reg4, 1, 2) > 12 Then
MsgBox "Plese enter a valid date (mm/dd/yyyy)."
Cancel = True
Exit Sub
End If
End Sub
Private Sub Reg1_Change()
Dim index As Integer
index = Reg1.ListIndex
Reg2.Clear
Select Case index
Case Is = 0
With Reg2
Reg2.List = Sheet4.Range("B2:B40").Value
End With
Case Is = 1
With Reg2
Reg2.List = Sheet4.Range("C2:C21").Value
End With
Case Is = 2
With Reg2
Reg2.List = Sheet4.Range("D2:D20").Value
End With
Case Is = 3
With Reg2
Reg2.List = Sheet4.Range("E2:E19").Value
End With
Case Is = 4
With Reg2
Reg2.List = Sheet4.Range("F2:F20").Value
End With
Case Is = 5
With Reg2
Reg2.List = Sheet4.Range("G2:G3").Value
End With
Case Is = 6
With Reg2
Reg2.List = Sheet4.Range("H2:H7").Value
End With
Case Is = 7
With Reg2
Reg2.List = Sheet4.Range("I2:I3").Value
End With
Case Is = 8
With Reg2
Reg2.List = Sheet4.Range("J2:J3").Value
End With
End Select
End Sub
Private Sub Cmdadd_Click()
Dim X As Integer
Dim nextrow As Range
Dim cNum As Integer
Dim M_Item As String
Dim M_Date As Date
Dim LastRow As Long
Dim rw As Integer
M_Date = Me.Controls("Reg" & 4).Value
M_Item = Me.Controls("Reg" & 2).Value
LastRow = Sheet1.Cells(Sheet1.Rows.Count, "B").End(xlUp).Row
Application.ScreenUpdating = False
For rw = LastRow To 2 Step -1
If Cells(rw, "B") = M_Item And Cells(rw, "D") = M_Date Then GoTo Passem
Next rw
GoTo NO_Dups
Passem:
Application.ScreenUpdating = True
MsgBox "This Item " & M_Item & " and Date " & M_Date & " were used previously, try again."
Exit Sub
NO_Dups:
For X = 1 To cNum
nextrow = Me.Controls("Reg" & X).Value
Set nextrow = nextrow.Offset(0, 1)
Next
'clear
For X = 1 To cNum
Me.Controls("Reg" & X).Value = ""
Next
On Error GoTo 0
Exit Sub
errHandler:
MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " _
& Err.Number & vbCrLf & Err.Description & vbCrLf & _
"Please notify the administrator"
End Sub
I got great help from Perpa but still couldn't make the form work since it shows there is a type mismatch in the "M_Date = Me.Controls("Reg" & 4).Value" , seems I have missed something in the code and would be great if I can find out what is the reason causing the problem, thank you!