Automation Error At Odd Spot in Code Execution

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

Private Sub wo_prep_name_afterupdate()
    If Application.EnableEvents = False Then Exit Sub
    If Len(wo_prep_name.Value) < 1 Then
        MsgBox "Please enter a name."
    End If
    wshds.Range("AD" & RowNo) = wo_prep_name.Value
End Sub

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.

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

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.

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

 ' prepare master worksheet
 
    Call dispatch_prep
    
 ' save dataset
    Call save_dataset2
    Worksheets("Frontpage").Activate
    Unload Me
End Sub

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

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.

Private Sub wo_gm_name_afterupdate()
    If Application.EnableEvents = False Then Exit Sub
    If Len(wo_gm_name.Value) < 1 Then
        MsgBox "Please enter a name."
    End If
    wshds.Range("W" & RowNo).Value = wo_gm_name.Value
    wshds.Range("X" & RowNo).Value = WorksheetFunction.VLookup(wo_gm_name.Value, Worksheets("StaffMaster").Range("L4:M20"), 2, False)
End Sub

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

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.

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.

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