Adding row in excel through User Form

[Damian37]

Hello Everyone,
I've written the following code, but I keep receiving a "Runtime error '91': Object variable or With block variable not set". I've bolded and italicized the portion of the code that is highlighted by the debugger. I'm not sure what's going on, because I believe I've ended the with statement correctly, and I've used this code for a previous user form that is working without issue. Any and all help is greatly appreciated. Thank you!
<code>

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
  Me.txtDate.SetFocus
  MsgBox "Please enter a Date. Thank you."
    Exit Sub
ElseIf Not IsDate(Me.txtDate.Value) Then
    Me.txtDate.SetFocus
    MsgBox "Please enter date in correct format. Thank you."
    Exit Sub
Else
    Me.txtDate.Value = Format(Me.txtDate.Value, "mm/dd/yyyy")
Exit Sub
End If

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

Else
Exit Sub
End If
End With
'clear the data
Me.txtDate.Value = ""
Me.txtPERNR.Value = ""
Me.txtName.Value = ""
Me.txtAmt.Value = ""
Me.txtComments.Value = ""
End Sub

Damian37
</code>

 

[Norie]

Is there any data on the sheet 'Data'?

If there isn't you'll get the error you indicate.

Try using this to get the next empty row instead.

lRow = WS.Range("B", Rows.Count).End(xlUp).Row + 1

 

[Damian37]

Is there any data on the sheet 'Data'?

If there isn't you'll get the error you indicate.

Try using this to get the next empty row instead.

lRow = WS.Range("B", Rows.Count).End(xlUp).Row + 1

Hi Norie,
Thank you so much for reminding me I never added headers to my columns within my data tab, and that's why I was receiving the error. However, I've since added the headers to the data tab, and I've run the code entered data within the fields, but the data format check that I added to the code doesn't appear to be working. Also when I click the Add button, the information entered into each field just stays there, it doesn't unload into the data tab, and the fields do not clear. Here's my updated code:

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
  Me.txtDate.SetFocus
  MsgBox "Please enter a Date. Thank you."
    Exit Sub
ElseIf Not IsDate(Me.txtDate.Value) Then
    Me.txtDate.SetFocus
    MsgBox "Please enter date in correct format. Thank you."
    Exit Sub
Else
    Me.txtDate.Value = Format(Me.txtDate.Value, "mm/dd/yyyy")
Exit Sub
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
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
Else
Exit Sub
End If
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 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 txtDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
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 for all of your help! Damian

 

[Norie]

Why do you have 3 Exit Subs here?

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

Shouldn't you only exit the sub if txtDate is blank or doesn't contain a date?

 

[Damian37]

Why do you have 3 Exit Subs here?

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

Shouldn't you only exit the sub if txtDate is blank or doesn't contain a date?

I'm sorry. lol, I didn't realize that I had 3 Exit Subs within the code I sent you. However, I don't believe that's what is stopping the date format check that I have, or the "Add" button not entering the data into the worksheet. I cannot thank you enough for the help you've given me thus far.

Damian

 

[Norie]

Damian

I think it might be where the problem lies.

In that section of the code even if a valid date is entered then the sub will be exited and nothing written to the worksheet.

The only thing that might change if a valid date is entered is the format of the date.

Try removing the third Exit Sub.

 

[Damian37]

Damian

I think it might be where the problem lies.

In that section of the code even if a valid date is entered then the sub will be exited and nothing written to the worksheet.

The only thing that might change if a valid date is entered is the format of the date.

Try removing the third Exit Sub.

Norie,
I'm no longer getting errors, the date format checks appear to be working (other then not placing cursor back in date field when an incorrect date format is entered). However the bigger issue I'm unable to figure out is why when I click on the "Add" button the data isn't entered into the "Data" WS and clearing from the user form fields.

Damian