Run-time error '1004': Application-defined or object-defined error

[Damian37]

Hello everyone,
I've been working on code for a userform that I've created. I've received quite a bit of help in figuring out why checks I put in, and other code weren't working. I've since gotten those issues fixed, however, now I'm receiving a run-time error, with the following code being highlighted by the debugger:

.Cells(lRow, 2).Value = Me.txtDate.Value

Here's the code it's in entirety. I've added a Record ID column within the "Data" worksheet, and headers within the first column. Would that be what's causing this error when the code is attempting to unload the data into the worksheet?

Private Sub CommandButton1_Click()
Exp_Log_Form.Show
End Sub
Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long
Dim WS As Worksheet
Set WS = Worksheets("Data")
'find first empty row in database
lRow = WS.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
lDate = Me.txtDate.Value
'check for a date
If Trim(Me.txtDate.Value) = "" Then
  MsgBox "Please enter a Date. Thank you."
    Me.txtDate.SetFocus
    
ElseIf Not IsDate(Me.txtDate.Value) Then
    MsgBox "Please enter date in correct format. Thank you."
    Me.txtDate.SetFocus
    
Else
    Me.txtDate.Value = Format(Me.txtDate.Value, "mm/dd/yyyy")
    Me.txtDate.SetFocus
    
End If
If DateValue(Me.txtDate.Value) > Date Then
    Cancel = True
    MsgBox ("Please enter either today's date or a date prior to today. Thank you.")
ElseIf DateValue(Me.txtDate.Value) <= Date - 6 Then
    Cancel = True
    MsgBox ("Please enter a date no older than 5 days from today. Thank you.")
    'Exit Sub
End If
'Exit Sub
With WS
  If MsgBox("Is all data correct?", vbYesNo) = vbYes Then
  .Cells(lRow, 2).Value = Me.txtDate.Value
  .Cells(lRow, 3).Value = Me.txtPERNR.Value
  .Cells(lRow, 4).Value = Me.txtName.Value
  .Cells(lRow, 5).Value = Me.txtAmt.Value
  .Cells(lRow, 6).Value = Me.txtComments.Value
 
End If
Exit Sub
End With
'clear the data
Me.txtDate.Value = ""
Me.txtPERNR.Value = ""
Me.txtName.Value = ""
Me.txtAmt.Value = ""
Me.txtComments.Value = ""
End Sub
Private Sub cmdClose_Click()
    Unload Me
    Me.txtDate.Value = ""
    Me.txtPERNR.Value = ""
    Me.txtName.Value = ""
    Me.txtAmt.Value = ""
    Me.txtComments.Value = ""
    
End Sub
Private Sub txtDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Trim(Me.txtDate.Text) = "" Then
    Cancel = True
MsgBox ("Please Enter A Valid Date. Thank you.")
    Me.txtDate.SetFocus
ElseIf Not IsDate(Me.txtDate.Value) Then
    MsgBox "Please enter date in correct format. Thank you."
    Me.txtDate.SetFocus
Else
    Me.txtDate.Value = Format(Me.txtDate.Value, "mm/dd/yyyy")
    Me.txtDate.SetFocus
'End If
End If
End Sub
Private Sub Label4_Click()
Exp_Log_Form.Show
End Sub
Private Sub txtPERNR_AfterUpdate()
If Len(Me.txtPERNR.Text) = 8 Then
    txtName.Enabled = True
        Me.txtName.SetFocus
MsgBox ("Please enter your name.")
End If
End Sub
'Private Sub txtPERNR_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'If Len(Me.txtPERNR.Text) <> 8 Or Trim(Me.txtPERNR.Text) = "" Then
'    Cancel = True
'MsgBox ("Please enter valid PERNR #. Thank you.")
'End If
'End Sub
Private Sub txtPERNR_LostFocus()
If Trim(Me.txtPERNR.Text) = "" Then
    Me.txtPERNR.Activate
MsgBox ("Please enter in valid PERNR #. Thank you.")
End If
End Sub
 

'Private Sub UserForm_Initialize()
'Me.txtDate.Value = Format(Date, "mm/dd/yyyy")
'End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
    Cancel = True
    MsgBox "Please use the Close Form button. Thank you."
  End If
End Sub

Thank you. Any and all help is greatly appreciated.
Damian

 

[Norie]

Damian

This code would be what's doing the automatic formatting but it's also checking for a valid date.

It also sets focus back to txtDate even if a valid date has been entered.

'Private Sub txtDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'If Trim(Me.txtDate.Text) = "" Then
    'Cancel = True
'MsgBox ("Please Enter A Valid Date. Thank you.")
    'Me.txtDate.SetFocus
'ElseIf Not IsDate(Me.txtDate.Value) Then
    'MsgBox "Please enter date in correct format. Thank you."
    'Me.txtDate.SetFocus
'Else
    'Me.txtDate.Value = Format(Me.txtDate.Value, "mm/dd/yyyy")
    'Me.txtDate.SetFocus
'End If
'End If
'End Sub

You also have this code in the CommandButton1 click event which is checking the date, formatting it and setting focus on txtDate.

If Trim(Me.txtDate.Value) = "" Then
  MsgBox "Please enter a Date. Thank you."
    Me.txtDate.SetFocus
    
ElseIf Not IsDate(Me.txtDate.Value) Then
    MsgBox "Please enter date in correct format. Thank you."
    Me.txtDate.SetFocus
    Me.txtDate.Value = Format(Me.txtDate.Value, "mm/dd/yyyy")
    
End If

So to be honest I'm a little confused.

I understand you want the user to enter a valid date but you need to decide when/where to check for that.