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

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

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

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.

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!

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.

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

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.

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.

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.

I think you should call Microsoft support.

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

I will test shortly Martin.

Martin - Unfortunately the same error resulted on all three machines.

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

Select allOpen in new window

No Sorry, actually it states 'LinkedCell' instead of 'ListFillRange'.

Here's a version that doesn't use LinkedCell. Changes are marked with "LinkedCell bug".
Q-28620650a.xlsm

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

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

Select allOpen 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.

Martin - Added the two lines you stated.

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

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

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

In the Visual Basic area go to the Immediate Window (ctrl+g)  and type

Application.EnableEvents = True

and press return.

Martin - Same results.

Forgot to add screenshot.
C--Users-dlehman-Desktop-Capture-5.JPG

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

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

Naw,

Not that either!

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.