?
Solved

Excel VBA:  Search / FIND feature using List Objects with Tables Run-Time Error 1004

Posted on 2012-09-06
6
Medium Priority
?
822 Views
Last Modified: 2012-09-08
Hi Everyone  :)

I've been having a doosey of a time trying to find a solution to my issue with my

attempt to have a userform have the capability of Searching through Records,

Populate the userform fields with the "found" record data, have the ability to delete or modify

the record, and if modified the ability to AMEND it to a table on a separate sheet

in the workbook... I know  "WHEW!" is right.  

I've found a solution but the functionality of the code is intermittent.  After

running some tests with the userform ... Entering a few new records, and Searching

and Modifying others and then returning back to entering new records I find that the

code hits a run time error:

It'll give Run-time error '1004':  Application-defined or object-defined error on

this line:
 
   
Set oNewRow = oLo.ListRows.Add(AlwaysInsert:=True)

Open in new window



Entire code is:

Private Sub cmdSubmission_Click()
    Select Case Me.cmdSubmission.Caption
    Case "Submit"
      
    Case "Amend"
     Me.EnableEvents = False
        lRw = lRw + 1
        oLo.ListRows(lRw).Range.Delete
         Set oLo = oSht.ListObjects("CFSData")
          Case Else: Exit Sub
    End Select
    nTime = -1
   
    Set oNewRow = oLo.ListRows.Add(AlwaysInsert:=True)
    With oNewRow
        .Range.Cells(1, 1).Value = Me.txtDate.Value
        .Range.Cells(1, 2) = Me.comboMgr.Value
        .Range.Cells(1, 3) = Me.comboSup.Value
        .Range.Cells(1, 4) = Me.comboSpecialist.Value
        .Range.Cells(1, 5) = Me.comboBehavior.Value
        .Range.Cells(1, 10) = Format(Me.lblTime.Caption, "hh:mm:ss")
        .Range.Cells(1, 12) = Me.optTotalAHT.Value
        .Range.Cells(1, 13) = Me.optCSAT.Value
        .Range.Cells(1, 14) = Me.optTransfers.Value
        .Range.Cells(1, 15) = Me.optCustIR.Value
        .Range.Cells(1, 11) = CurUser
    End With

    Me.cmdSubmission.Caption = "Submit"
    'clear the form
    '    cmdClear_Click
    LoadDataView
    
'clear & reset controls on the form
    ClearAll Me
    Me.txtDate.Value = Date
    Me.lblTime.Caption = Empty
    Sheets("Entry_Form").Range("AA2").Value = CurUser    '<- is this necessary?
    Sheets("Entry_Form").Range("AG13").Value = ""
    
End Sub

Open in new window


Could someone please assist with helping me determine what's causing the error..

I've searched through forums and online tech sites and various triggers came back.  

I don't have the table set on auto filter, the workbook isn't in "read only", the

sheet isn't "protected" ..  And it takes a around 10 (sometimes less) entries to duplicate the error.  I appreciate your time.

Any assistance would be greatly appreciated.  Thank you  :)

I've attached a mock workbook below
CFS-Tracker-TestUser-1.04x.zip
0
Comment
Question by:"Abys" Wallace
  • 3
  • 3
6 Comments
 
LVL 24

Expert Comment

by:Steve
ID: 38375202
Error 1004 is normally due to having the autofilter applied to the data which inserting / copying / pasting inside the table.
Try this before inserting
ActiveSheet.ShowAllData

Open in new window

0
 

Author Comment

by:"Abys" Wallace
ID: 38376392
@the_barman >>>  Thank you for the recommendation.  But where would I place the code?  I'm getting a Run-Time error 1004:  ShowAllData method of Worksheet class failed ...

Currently I have it here:

Private Sub cmdSubmission_Click()
    
    Select Case Me.cmdSubmission.Caption
    Case "Submit"
      
    Case "Amend"
     Me.EnableEvents = False
        lRw = lRw + 1
        oLo.ListRows(lRw).Range.Delete
         Set oLo = oSht.ListObjects("CFSData")
          Case Else: Exit Sub
    End Select
    nTime = -1
   
'code recommendation from EE

    ActiveSheet.ShowAllData

    Set oNewRow = oLo.ListRows.Add(AlwaysInsert:=True)
    With oNewRow
        .Range.Cells(1, 1).Value = Me.txtDate.Value
        .Range.Cells(1, 2) = Me.comboMgr.Value
        .Range.Cells(1, 3) = Me.comboSup.Value
        .Range.Cells(1, 4) = Me.comboSpecialist.Value
        .Range.Cells(1, 5) = Me.comboBehavior.Value
        .Range.Cells(1, 10) = Format(Me.lblTime.Caption, "hh:mm:ss")
        .Range.Cells(1, 12) = Me.optTotalAHT.Value
        .Range.Cells(1, 13) = Me.optCSAT.Value
        .Range.Cells(1, 14) = Me.optTransfers.Value
        .Range.Cells(1, 15) = Me.optCustIR.Value
        .Range.Cells(1, 11) = CurUser
    End With

    Me.cmdSubmission.Caption = "Submit"
    'clear the form
    '    cmdClear_Click
    LoadDataView
    
'clear & reset controls on the form
    ClearAll Me
    Me.txtDate.Value = Date
    Me.lblTime.Caption = Empty
    Sheets("Entry_Form").Range("AA2").Value = CurUser    '<- is this necessary?
    Sheets("Entry_Form").Range("AG13").Value = ""
    
End Sub

Open in new window

0
 
LVL 24

Expert Comment

by:Steve
ID: 38376946
you may need to select a cell within the range of the table.
Then the code should work.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:"Abys" Wallace
ID: 38378277
@The_Barman ... thank you for your advise.. I selected a cell in the Active sheet but I'm still getting the same error (Run time 1004:  ShowAllData method of Worksheet class Failed ...
0
 
LVL 24

Accepted Solution

by:
Steve earned 2000 total points
ID: 38378855
OK, then rather than clearing the autofilter will have to turn it off...

Worksheets("<SheetName>").AutoFilterMode = False

Open in new window


Then will need to remember to turn it back on

  If Not ActiveSheet.AutoFilterMode Then
    ActiveSheet.Range("A1").AutoFilter
  End If

Open in new window


This should prove / disprove autofilter as the cause of the issue.
0
 

Author Closing Comment

by:"Abys" Wallace
ID: 38379592
Thank you!  After running several tests it works great!  @The_Barman
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question