"Automation Error" on this line of code; how to fix?

birdieman · Mar 24, 2016

I have a workbook and userform that, when opened, recently starting crashing Excel, usually without telling me why, but the last time gave me an “Automation Error”.

The story: I can open the file holding down the shift key, and “run” the userform/program from the VB editor without problems – all initialized data taken from spreadsheet is correct. I can also then close the file (keeping Excel open), open it normally (just clicking on the file), use the useform, close it (keeping Excel open), save it, over and over with no problem. However, after I close/quit Excel, then open Excel, then try to open my file, Excel crashes on a particular line of code in the userform initialization (all the initialization does is load saved date from worksheet to form). I have the same issues with this file on another computer (different operating system and different version of Excel).

This is the line of initialization code (happens to be about line 60^th line in the initialization -give or take) on which the error occurs (other lines left out). When I comment out this line of code, the program opens fine every time.

Code:

TextBox357.Value = ufd.Range("a66")

The line that saved the data to the worksheet.

Code:

ufd.Range("a66") = TextBox357.Value

I have verified in the worksheet that the data is there and it is correct, and that the worksheet has no errors when using that cell’s data. The program compiles fine. The workbook_open code:

Code:

Private Sub Workbook_Open()
Application.OnTime Now + TimeSerial(0, 0, 2), Me.CodeName & ".LoadTheForm"
End Sub

Private Sub LoadTheForm()
UserForm1.Show
End Sub

TexBox357 happens to be a textbox that was added about two months after the userform was created. I thought maybe the textbox was “dead” (whatever that means) so I deleted it and added another – same problem, same line.
A couple of times I received the “automation error”; mostly, however, Excel just quits and says nothing but “Excel has encountered an error and must shut down”. I clicked the “help” box in the automation error code, but did not copy what it said. I Googled ‘automation error’, and here is what it said (my error message did not have OLE in front of automation – so it may not be the same thing):
[FONT=&quot]
“CAUSE[/FONT]

[FONT=&quot]This problem occurs when you access a Microsoft Excel OLE Automation object in a Visual Basic procedure while Microsoft Excel is closed. If, for example, you create a reference for a worksheet object using the CreateObject function, and you create a reference for another Microsoft Excel object using the GetObject function in your procedure, if you then set the Microsoft Excel worksheet object (Excel.Sheet) equal to nothing, you receive the OLE Automation error message if you then try to access the other object.

This problem occurs because setting a Microsoft Excel object that was created using the CreateObject function equal to Nothing closes Microsoft Excel, even if your Visual Basic procedure still has a reference to another Microsoft Excel object. “[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]I am relatively new to VBA, so I do not know what any of this means. I do not use “createObject” in my code. My code is simple textbox stuff, except for a couple of things I copied from this forum, but those things have been working for months without problems.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Sorry for the length, but wanted to give someone all the info at once. Thanks for looking.[/FONT]

"Automation Error" on this line of code; how to fix?

birdieman

Well-known Member

Excel Facts

Similar threads

Forum statistics

Share this page

"Automation Error" on this line of code; how to fix?

birdieman

Well-known Member

Excel Facts

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock