Mysterious ActiveX component error

[Engberg]

I'm working on a document search tool for internal use at my company. The tool consists of one main workbook which contains all of the code and the interface (a UserForm), and a database.xlsx which is hidden and only open in the background and contains information and search terms for each document that can be searched for/opened.

While testing, I've ran into Error 429: ActiveX component can't create object. This only happens in a very specific scenario:

1. Have any other Excel spreadsheet open
2. Open the search tool workbook
3. Close the search tool workbook
4. Opening the search tool workbook once more gives me Error 429 in Workbook_Open() on Line 0.

If I don't have another spreadsheet open, or close the other spreadsheet after opening the search tool for the first time, the error doesn't occur.

Any ideas on how to remedy this?

 

[Engberg]

Well, there is no more code to step through. My error handler still tells me that UserForm1.Show is the issue. Can I put in breaks in Workbook_Open to F8 through the code myself to see if it jumps into UserForm_Activate or not?

All lines of code after UserForm1.Show in Workbook_Open seem to be working fine. When Workbook_Open has finished, the UserForm is gone, and the Excel application is still hidden. This is expected, since I have Application.Visible = False on Workbook_Open and the user form couldn't be created.

My error handler:

Public Function ErrorHandler(subName As String)
    MsgBox ("Ett oväntat fel uppstod." & vbNewLine & vbNewLine & "Kontrollera att du har tillgång till internet och testa stänga alla andra Excel-filer." & vbNewLine & "Du kan behöva starta om applikationen för att den ska fungera korrekt. ")
    Call WriteToErrorLog("-------------------- " & Date & " " & Time & " " & Application.UserName & " FEL UPPSTOD PÅ LINJE " & Erl & vbNewLine & "SUB/FUNKTION: " & subName & vbNewLine & "FELKOD: " & Err.Number & vbNewLine & "FELBESKRIVNING: " & Err.Description & vbNewLine & "-------------------- " & Time & " " & Application.UserName & vbNewLine)
    Call WriteToSequenceLog(Time & " " & Application.UserName & "  ! ! ! ! ! ! ! ! Felkod " & Err.Number & " i sub/funktion " & subName & " i och med händelsen ovan")
End Function

This issue as a whole doesn't exist if I don't try to close the search tool workbook when the user closes the user form. If I leave it up to the user to also close the workbook itself, this is a non-issue. I thought it would be neat to only ever show the user form and not the spreadsheet to the user, to make it feel more like a stand-alone application rather than an Excel-file but at this point I might just have to accept it.

PS. How do you format that little box?

 

[RoryA]

What I mean is that when the code breaks on userform1.show and you debug (so that line is highlighted), if you press f8 does the cursor jump to code in the userform_activate event, or do you simply see the error message again?

To highlight individual code words, use the Inline code button to the left of the XL2BB icon.

 

[Engberg]

When the code breaks on UserForm1.Show and I debug so that the line is highlighted, F8 simply continues down the Workbook_Open sub to subsequent lines. I don't see the error message again and it doesn't enter the userform_activate event.

 

[RoryA]

That's very strange indeed. Any chance you can put a copy of the workbook that shows the issue somewhere (no data necessary) for me to have a look at?

 

[Engberg]

I have made a shareable version of the project which still produces the issue. I won't post the download link in this thread but can send it to anyone who wants to have a look at it.

 

[RoryA]

The issue is definitely to do with closing the workbook before the form is fully unloaded. If you simply remove End from the QueryClose event and change the QuitApplication line to Application.Ontime Now, "QuitApplication", the problem should go away. You should also really be using a variable for the form object rather than relying on the default instance, and use Me within the form code rather than Userform1.

 

[Engberg]

The issue is definitely to do with closing the workbook before the form is fully unloaded. If you simply remove End from the QueryClose event and change the QuitApplication line to Application.Ontime Now, "QuitApplication", the problem should go away. You should also really be using a variable for the form object rather than relying on the default instance, and use Me within the form code rather than Userform1.

Hey, big thanks.

Finally got the time to implement these changes, it solved the issue. I understand how closing the workbook before the form fully unloads can cause issues, but why does changing
Call QuitApplication to Application.Ontime Now, "QuitApplication" work? How do they differ?

 

[RoryA]

OnTime schedules the code to run at the specified time. That just gives the form a chance to actually unload before the workbook is closed.