Adding row in excel through User Form

D

Damian37

Active Member
Joined
Jun 9, 2014
Messages
301
Office Version
  1. 365
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>
Rich (BB 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>
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
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.
Code: 
lRow = WS.Range("B", Rows.Count).End(xlUp).Row + 1
 
Upvote 0
Norie said:
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.
Code: 
lRow = WS.Range("B", Rows.Count).End(xlUp).Row + 1
Click to expand...

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:

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
 
Upvote 0
Why do you have 3 Exit Subs here?
Code: 
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?
 
Upvote 0
Norie said:
Why do you have 3 Exit Subs here?
Code: 
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?
Click to expand...

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
 
Upvote 0
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.
 
Upvote 0
Norie said:
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.
Click to expand...

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
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Moving Rows Up (Scroll) To Defined Row Number for Viewing
Replies
6
Views
195
Ark68
A
E
  • Question
Trying to use a month name in my VLookup
Replies
2
Views
145
Eric Penfold
E
tourless
  • Question
Capture and remove last two in a string
Replies
3
Views
491
duggie33
D
P
  • Solved
Excel VBA, Vlookup to another sheet in same work book
Replies
6
Views
176
StephenCrump
S
J
  • Solved
VBA stopped working through Sharepoint
Replies
7
Views
450
RoryA
RoryA

Forum statistics

Threads
1,215,020
Messages
6,122,709
Members
449,093
Latest member
Mnur

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top