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

D

Damian37

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

Rich (BB code): 
.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?

Rich (BB 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
  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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
Damian

What's the value of lRow when you get the error?
 
Upvote 0
Norie said:
Damian

What's the value of lRow when you get the error?
Click to expand...

Hi Norie,
I'm sorry, I don't understand the question. Currently I'm not getting a value for lRow when I get the error. The subject line has the exact error I'm receiving right now.
 
Upvote 0
The only way I can see that line causing an error is if there is a problem with the value of iRow.

That's why I'm asking what it's value is when you get the error?

By the way, what exactly do you have in the RecordID column?
 
Upvote 0
Norie said:
The only way I can see that line causing an error is if there is a problem with the value of iRow.

That's why I'm asking what it's value is when you get the error?

By the way, what exactly do you have in the RecordID column?
Click to expand...

I set up the worksheet to be a table. The Record ID column tracks the number of entries per month giving us the ability to identify busier months versus less busier months. I've modified the code, and now the VBA code is working in adding a record to the "Data" worksheet, however, the data within the fields aren't clearing after the "Add" button has been clicked. The userform also remains open after the data has been entered in to the next available row. I've been unable to figure out why the fields aren't clearing, and the userform is staying open. Do I need to add another command for the "Add" button? As in a "close userform" command? Again, I cannot thank you enough for all of your assistance with this. I haven't really been able to utilize VBA scripting as much as I would like at work, so my learning has been extremely slow. Your patience is also appreciated.
Damian
 
Upvote 0
Damian

So you aren't having the '1004' error now, but having other problems?
 
Upvote 0
Norie said:
Damian

So you aren't having the '1004' error now, but having other problems?
Click to expand...

You are correct. The userform is entering in data within the "Data" worksheet, however, the data isn't being cleared from the fields within the userform after the "Add Expense" button is clicked. I thought the unload portion of my code for the "Add Expense" button would clear the form, but it isn't. Could it be I have an "End Sub" within the wrong area in the sub query? I'm also dealing with a formula that I created to add another Record ID number automatically to the new row being added by the userform, but that doesn't seem to be working either. lol. I'm all kinds of messed up here Norie.
Damian
 
Upvote 0
Norie said:
Damian

So you aren't having the '1004' error now, but having other problems?
Click to expand...

Hi Norie,
I'm no longer getting the error, and the data is being populated when using the userform. The fields are also clearing when I hit the "Add Expense" button, however, I had code in there before that changed the format of the date to be a 4 digit year instead of a 2 digit year. It was changing the format automatically before, and now for some reason it's stopped. I believe the code I have commented out having to do with date format was automatically changing the format from 2 digit to 4, but when I leave that code snippet uncommented I get an error that a date is necessary before you can close the form using the close button. I've added the updated code for your review. Thank you very much for all of your assistance with this project Norie. Truly!

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
  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
    
 'If Trim(Me.txtDate.Value) = Format(Me.txtDate.Value, "mm/dd/yyyy") Then
    '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
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 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_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

Damian
 
Upvote 0
Damian

Why are you checking the date twice?
 
Upvote 0
Norie said:
Damian

Why are you checking the date twice?
Click to expand...
I commented a lot of the previous date coding out. Sorry, I forgot to remove the commented lines out. I believe I'm only checking the date once, and right now I'm trying to control the format of the date. So, my date format was changing to the format I wanted until I commented the second date coding out, and the auto-formatting just stopped, and I'm not sure why.

Damian
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Exiting a Textbox Problem
2
Replies
10
Views
241
Pacman52
P
P
  • Solved
Advice needed on where to put code that stops duplicate entries
Replies
5
Views
321
Pacman52
P
D
  • Solved
Run-time error '1004': Application-defined or object-defined error
Replies
4
Views
490
rollis13
rollis13
M
  • Question
Add in VBA Code to check zip code against pre-determined list
Replies
0
Views
169
mikefisher
M
S
  • Solved
"Run-time error '91': Object variable or With block variable not set" error
Replies
5
Views
181
Micron
M

Forum statistics

Threads
1,215,201
Messages
6,123,621
Members
449,109
Latest member
Sebas8956

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