Solved

excel 2010 error -2147467259 (Method 'ListFillRange' of object '_ OLEObject' failed) in procedure ShowAutocomplete

Posted on 2015-02-19
30
373 Views
Last Modified: 2015-03-12
User A opens attached excel file that has script for Data Validation in the "Type" column.

Their system has MS Office 2010 installed with Outlook 2013 - both at latest patch levels, including Windows.

When they click on any cell on sheet they receive the other attached screenshot error. They click ok then click in the cell again and it functions, except the "Type" does not act as it is scripted to.

User A then logs into a Terminal Service session, Office 2010 with no Outlook 2013. Office 2010 patched to latest level. The entire sheet works perfect!  EH?

Thanks in advance.
C--Users-dlehman-Desktop-Q-28619595a.xls
C--Users-dlehman-Desktop-Capture.jpg
0
Comment
  • 12
  • 10
  • 6
  • +1
30 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 40620852
I don't know maybe it is this but weird it works without Outlook2013 and not with it do not see any use of outlook in your macro unless you can indicate where.

Ok I have changed in Sub ShowAutocomplete

this line
.ListFillRange = strVF

by this line
.ListFillRange = "=" & strVF

Last you attached the file as .xls but the format is .xlsm.
try it and let me know. File attached.

gowflow
C--Users-dlehman-V01.xlsm
0
 
LVL 13

Expert Comment

by:John Mc Hale
ID: 40620947
Hi gowflow,

Make sure that that:

1. The user dlehman has sufficient provileges on the file, and
2. There is sufficient free disk space on the drive hosting the Excel file.

See the following URL w.r.t. error -2147467259 (0x80004005).

http://www.windowsanswers.net/slimware/fix-0x80004005
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40621441
See the following URL w.r.t. error -2147467259 (0x80004005).
The article mentions a virus as one of the possible reasons for the error and it suggests downloading what I assume is a virus cleaner called SlimCleaner Plus. I suppose a virus is a possibility (even though you'd probably see other symptoms if it were) but I thought I'd point out that the article is sponsored by SlimWare, the maker of SlimCleaner Plus.

Ad
0
 
LVL 13

Expert Comment

by:John Mc Hale
ID: 40621512
Yes (and without wishing to bash Symantec Endpoint Protection over the head), the Real Time File Protection feature often causes havoc with routine file operations that would normally pass off without incident.

This is also a very real possibility given that the Excel workbook clearly contains code, and is potentially coming from an untrusted network source via Outlook!
0
 

Author Comment

by:haradaindustryofamerica
ID: 40621540
Thanks all, I will test those theories. We are actually running Symantec Endpoint on all systems so that could be the cause.

My system is not running Symantec, neither is the TS system.

Going to run tests. Will inform you of results.
0
 
LVL 13

Expert Comment

by:John Mc Hale
ID: 40621555
My agency uses Symantec Endpoint Protection as the corporate anti-virus/malware. I regularly have to disable it when conducting forensic analysis of suspect evidence.

Regards.

john
0
 

Author Comment

by:haradaindustryofamerica
ID: 40629288
Status update -  Symantec testing has shown that it is not the cause.

I have tried several different systems all using the same userid and different settings of Symantec EP, all the same.
I have even removed completely removed Symantec EP, still the same results.

My testing is next going along the lines of Outlook 2013 combined with already installed Office 2010.

All systems have Office 2010 full install on them. At end of last year we upgraded the Outlook 2010 to Outlook 2013. We did not remove Outlook 2010, we just upgraded it.

All of these machines have the same result on the file with the error message.

Now - we have several new machines that after imaging we installed Office 2010 minus the Outlook 2010 portion and installed Outlook 2013 fresh. These machine the file works perfectly on, no errors, no issues.

Very odd.

Going to run test on known bad systems with removing Office 2010 and Outlook 2013 completely and then re-install and see what happens.

Any ideas anyone?   I would hate to have to do that to all machine in the environment if the test is successful.
0
 
LVL 13

Expert Comment

by:John Mc Hale
ID: 40629451
Automation errors can be notoriously difficult to diagnose, particularly when the HRESULT is 0x80004005; which is basically a way of informing the user that something bad happened but it can't be more specific.

It is always a bad idea to "overinstall" software on top of other versions. If I were hunting this "bug" down, I would probably look towards calling the Windows API function GetLastError() from an error handler in the Macro/Script to see if this can provide additional information as to the core cause of the problem. It could be any number of things including a missing dll, unregistered OCX etc. It's impossible to say without debugging in the environment.

Also, the fact that the Excel workbook is being opened in a Terminal services session may have something to do with it. I vaguely recall having problems with file sharing in Terminal services, but this was with a Microsoft Access database front-end that several users were trying to access at the same time.
0
 

Author Comment

by:haradaindustryofamerica
ID: 40646874
Ok - Testing Update.

It appears it has something to do with installing Office 2010 Pro and then afterwards doing an upgrade install of only Outlook to version 2013.

I have tested on 3 separate systems that I have re-imaged and tested several times.

Testing on all 3 consisted of:
- Complete install of Office 2010 Pro on Windows 7 workstation. The excel sheet works fine.
- Upgrade install run of Outlook 2013. So that Office level is 2010 Pro but Outlook is 2013. The excel sheet produces error.

- Complete install of Office 2013. Excel sheet works fine.

- Install of Office 2010 Pro minus the Outlook software. The excel sheet works fine.
- Then install full package of Outlook 2013 (not upgrade), the excel sheet works fine. So installing the Outlook 2013 clean - not an upgrade - and the excel sheet works fine.

- On systems that were producing error with excel sheet - Complete uninstall of all Office and Outlook software.
- Then re-install Office 2010 Pro minus the Outlook. The excel sheet is still producing error.
- Then install of Outlook 2013 - not upgrade - the excel sheet still produces error.

So it appears that the upgrade and not a clean install of Outlook 2013 is somehow causing this error.
I have searched all Event Logs and log files that I know of and there is no message pertaining to this error in any of the. The Windows and Microsoft Event Logs - nothing.

Is there any other log file to examine that could point me I the right direction?

Thanks.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40646912
I think you should call Microsoft support.
0
 
LVL 45

Assisted Solution

by:Martin Liss
Martin Liss earned 250 total points
ID: 40646932
I may have found a solution for you. Be back soon.
0
 
LVL 29

Accepted Solution

by:
gowflow earned 250 total points
ID: 40646935
I doubt he would get anywhere by calling. My suggestion just do it the way you were able to succeed with
ie
- Install of Office 2010 Pro minus the Outlook software. The excel sheet works fine.
 - Then install full package of Outlook 2013 (not upgrade), the excel sheet works fine. So installing the Outlook 2013 clean - not an upgrade - and the excel sheet works fine.

gowflow
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40646977
Ok if the problem/bug is with the ListFillRange method and not the filling of the combobox itself, then the attached modification which fills the combobox via a loop may work for you. The changes are marked with "ListFillRange bug".
Q-28620650.xlsm
0
 

Author Comment

by:haradaindustryofamerica
ID: 40647035
I will test shortly Martin.
0
 

Author Comment

by:haradaindustryofamerica
ID: 40647416
Martin - Unfortunately the same error resulted on all three machines.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 45

Expert Comment

by:Martin Liss
ID: 40647444
When you say the "same error" do you mean that it referred to ListFillRange? If so you then I'm mystified because the workbook I posted has all the references to ListFillRange commented out as shown here in the code from the workbook.

Public Sub ShowAutocomplete(Target As Range)
    Dim strVF As String
    Dim cboTemp As OLEObject
    Dim ws As Worksheet
    '******* ListFillRange bug Start *******
    Dim cel As Range
    '******* ListFillRange bug End *********
    
    On Error GoTo errHandler

    Set ws = ActiveSheet
    
    If gbMaintBeingDone Then
        Exit Sub
    End If
    
    Set cboTemp = ws.OLEObjects("TempCombo")
    'On Error Resume Next
    With cboTemp
        ' Clear and hide the combo box
        '******* ListFillRange bug Start *******
'        .ListFillRange = ""
        '******* ListFillRange bug End *********
        .LinkedCell = ""
        .Visible = False
    End With
    
    'new
    If Not Intersect(ActiveCell, Range("C12:C21")) Is Nothing Then

        If Target.Validation.Type = 3 Then
            ' The cell contains a data validation list
            Application.EnableEvents = False
            ' Get the data validation formula
            strVF = Target.Validation.Formula1
            strVF = Right(strVF, Len(strVF) - 1)
            With cboTemp
                ' Show the combobox with the list
                .Visible = True
                .Left = Target.Left
                .Top = Target.Top
                .Width = 130
                .Height = Target.Height
                '******* ListFillRange bug Start *******
'                .ListFillRange = strVF
                '******* ListFillRange bug End *********
                .LinkedCell = Target.Address
            End With
            '******* ListFillRange bug Start *******
            With Sheets("Part Order Form").TempCombo
                .Clear
                For Each cel In Range(strVF)
                    .AddItem cel.Value
                Next
            End With
            '******* ListFillRange bug End *********
            cboTemp.Activate
            ' Open the drop down list automatically
            ActiveSheet.TempCombo.DropDown
            'new
            'Set grngLinkedCell = Target
        End If
    
   'new
   End If
    
    Application.EnableEvents = True
    On Error GoTo 0
    Exit Sub

errHandler:

    Application.EnableEvents = True
    ' If it's 1004 there's no data validation in the cell
    If Err.Number <> 1004 Then
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ShowAutocomplete"
    End If
    
End Sub

Open in new window

0
 

Author Comment

by:haradaindustryofamerica
ID: 40647449
No Sorry, actually it states 'LinkedCell' instead of 'ListFillRange'.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40647522
Here's a version that doesn't use LinkedCell. Changes are marked with "LinkedCell bug".
Q-28620650a.xlsm
0
 

Author Comment

by:haradaindustryofamerica
ID: 40653896
Martin - Get a debug error now.

Please see attached.
C--Users-dlehman-Desktop-Excel-Error-1.J
C--Users-dlehman-Desktop-Excel-Error-2.J
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40653993
Add lines 16 and 18.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim str As String
    Dim cboTemp As OLEObject
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Application.EnableEvents = False
    Application.ScreenUpdating = True

    If Application.CutCopyMode Then
        'allow copying and pasting on the worksheet
        GoTo errHandler
    End If
    
    '******* LinkedCell bug Start *******
    On Error Resume Next
    grngLinkedCell = Sheets("Part Order Form").TempCombo.Value
    On Error GoTo 0
    '******* LinkedCell bug End *********
    
    ShowAutocomplete Target

errHandler:
    Application.EnableEvents = True
    Exit Sub

End Sub

Open in new window


It didn't experience that error when I tested the workbook I attached above probably because I had been working with the workbook a while before I added that code. It did happen just now when I reopened the workbook so it seems to be a startup problem. After you make the above change if it happens to you at any time after the first time you select a "Type" cell please let me know.
0
 

Author Comment

by:haradaindustryofamerica
ID: 40654042
Martin - Added the two lines you stated.

see attached for current message when selecting "Type".
C--Users-dlehman-Desktop-Capture-3.JPG
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40654107
Please tell me or show me where that error occurs. And just in case there's any confusion here's a workbook where I made and tested that change.
Q-28620650b.xlsm
0
 

Author Comment

by:haradaindustryofamerica
ID: 40654316
Martin - Using the file you sent. Same error as soon as I click on cell C12. If I click "Ok" in the message box I then see the attached screenshot.

The dropdown validation list is small again and the larger text box remains as it is.

This happen if I select any cell in "Type" column.
C--Users-dlehman-Desktop-Capture-4.JPG
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40654332
In the Visual Basic area go to the Immediate Window (ctrl+g)  and type

Application.EnableEvents = True

and press return.
0
 

Author Comment

by:haradaindustryofamerica
ID: 40654838
Martin - Same results.
0
 

Author Comment

by:haradaindustryofamerica
ID: 40654841
Forgot to add screenshot.
C--Users-dlehman-Desktop-Capture-5.JPG
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40654917
I've attached an updated workbook where I've moved all the pertinent module code to the sheet and if this doesn't work then I'm sorry to have to say that I don't think I can do anything more for you since I can't reproduce your environment.
Q-28620650c.xlsm
0
 
LVL 13

Expert Comment

by:John Mc Hale
ID: 40659968
haradaindustryofamerica,

Another throw of the dice:

1. On the affected machines start Command prompt as Administrator.
2. CD to the Windows\SysWOW64 folder.
3. Type regsvr32 FM20.DLL and press ENTER.
4. Retry the .ListFillRange method in ShowAutocomplete.

Possible Explanation:

Somewhere in the installation of Outlook, the MSForms component has become unregistered. The above method should fix that
0
 
LVL 13

Expert Comment

by:John Mc Hale
ID: 40660003
Naw,

Not that either!
0
 

Author Comment

by:haradaindustryofamerica
ID: 40660897
Nothing has worked thus far. I appreciate all the help from everyone but I believe I am going to close this ticket and simply re-image the machines utilizing the install method above instead of the upgrade method for Outlook 2013. That seems to be the only thing that works.

Again thanks all.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

706 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now