Automation Error At Odd Spot in Code Execution

[Jenn68]

I am receiving an 'Automation Error" with (I think) the line of code highlighted below ...

Please Login or Register  to view this content.

I'm not sure what an automation error is, nor why I am getting one here. This is a procedure within a userform module that has been closed, I'm not sure why this particular code would be firing at this time.
Any thoughts?

Jenn

PS My apologies if the information provided is vague or inappropriate. This is a new error, and I'm not quite sure what I might need to include.

[JosephP]

first off I'd change it to

Please Login or Register  to view this content.

[Jenn68]

Hi Joseph,

Thank you for the suggestion. I changed it, and all other similar instances in this module. Unfortunately, the problem still exists.
Whats particularly odd, is that if I step manually through the procedure, I don't get the errors.

Jenn

[JosephP]

do you have any activex controls on sheets, or are any of the form controls directly linked to cells on sheets (using rowsource or Controlsource properties)?

[Jenn68]

I have some buttons on "Frontpage", but I don't believe them to be ActiveX. And yes, I do have several textboxes on the userform that have cell or named ranges within their rowsource properties.

[JosephP]

try disconnecting the controls from the ranges (populate them in code instead) and see if that helps.

[Jenn68]

I think I have isolated the problem to ....

Please Login or Register  to view this content.

If I remove this line, it doesn't err. But it also keeps my form open. I'd like to close it as it's no longer being used.

Jenn

[JosephP]

do you have any code in the terminate or queryclose events of the form, or any code following the line that loaded the form originally? posting a workbook would be very helpful I suspect, if you can.

[Jenn68]

Hi Joseph ... being a novice at Excel VBA, please excuse my ignorance, and let me express my appreciation for your help.

I have not used any terminate or queryclose events in my form. The only code following this is end sub lines from procedures that got us to this point.

Another observation I had made, is it will not err if I don't change any userform controls (ie textboxes) in the form. If I simply load the form, review the records without doing any editting via the userform, and save it, there is no error. If I change any of the user fields as part of the user's editting task, after saving, I get the error. It always returns the error on the first textbox control that was changed.

I question the appropriateness of using afterupdate, rather than change, for an event change.

Most of my userform textbox control change routines use the "afterupdate" event. Could that be the culprit? With the example below, "Please enter a name." will flash just prior to code break.

Please Login or Register  to view this content.

Sharing my workbook, as much as I wish I could, would be awkward. As it's part of an application, including external reference databases would be difficult, and going through the application to get to this point would be tedious for you. Hopefully this last revelation is another nail in the coffin of this problem.

Jenn

[JosephP]

I'm a bit bothered by the fact that your afterupdate event declaration is not properly capitalised. does your project compile ok?

[Jenn68]

With the exception of this error we are trying to isolate, I have not found anything unexpected or out of the ordinary. Is this what you mean by compile?
I've changed all these instances to the proper format of AfterUpdate.

[JosephP]

no, I mean selecting Debug-compile from the menu in the vbe. btw I'd probably use the Exit event of the controls rather than afterupdate

[Jenn68]

I have compiled my code as you had pointed out to me. What a great tool ... thanks for pointing it out to me. It discovered a lot of typos etc which would have eventually caused problems no doubt. None of them resolved this issue though. :-(

I have changed the AfterUpdates to Exits as suggested. When it reaches this line of changed code, I get the error: "Procedure declaration does not match description of event or procedure having the same name." I don't know what that means or if it's an improvement over the other error.

[JosephP]

what does the declaration look like? if you select the control in the left dropdown at the top of the main code window, and Exit from the right dropdown, it'll create the declaration for you.

[Jenn68]

Hi Joseph,

With all the testing I had been doing to straighten out this error, I opted to rewrite this section of code to implement some of your observations right off the bat rather then try to integrate them. Turns out I was creating more errors in my attempts to track down the original, that it deserved a rewrite. So, at this point, I abandon this thread. Hopefully, I'll avoid the posted error and not have to deal with again.

However, in my preliminary testing of my rewritten code, I've run into another "limitation". Not an error, but more an annoying limitation. Not sure if you'd like to tackle something new or not, but check out http://www.excelforum.com/excel-prog...n-routine.html.

Thanks for all your patience and support!

Jenn

[JosephP]

good call.
I just posted in your other one.