Error: automation error the object invoked has disconnected from its clients

[siroos12]

Good day all,

I built a vba userform in my excel file and frequently receiving "automation error the object invoked has disconnected from its clients" error. Some times it works and some times it is not. I have comboboxes in the form, by choosing the comboboxes some times it does not show the range on itself and some times it just give me "Run Time" Error and close the excel.
it does confuse me because it happens some times not always.

I do appreciate in advance for your help.

Cheers.

[siroos12]

I just read lots of articles bout this error but could not handle how to solve it. the latest article was "INFO: Error or Unexpected Behavior with Office Automation When You Use Early Binding in Visual Basic" so I considered i need to do qualifying my code but i do not know how?!

here is the codes which I wrote on a excel vba userform:

Private Sub CommandButton1_Click()
Application.Calculation = False
Worksheets("Data Base").Activate

    MSG1 = MsgBox("Do you want to submit the form?", vbYesNo)
    If MSG1 = vbYes Then
        Dim ssheet As Worksheet
        
        Set ssheet = ThisWorkbook.Sheets("Data Base")

        nr = ssheet.Cells(Rows.Count, 2).End(xlUp).Row + 1

            ssheet.Cells(nr, 2) = Me.TextStockNo
            ssheet.Cells(nr, 3) = Me.ComboRepName
            ssheet.Cells(nr, 5) = Me.DTPickerDate
            ssheet.Cells(nr, 6) = Me.TextYearModel
            ssheet.Cells(nr, 7) = Me.ComboVehicleMake
            ssheet.Cells(nr, 8) = Me.TextDescription
            ssheet.Cells(nr, 9) = Me.TextRegNo
            ssheet.Cells(nr, 9) = Me.TextMileage
            ssheet.Cells(nr, 11) = Me.ComboColour
            ssheet.Cells(nr, 12) = Me.TextBought
            ssheet.Cells(nr, 13) = Me.TextSold
            ssheet.Cells(nr, 15) = Me.ComboDealer
            ssheet.Cells(nr, 16) = Me.ComboAdvert
            ssheet.Cells(nr, 17) = Me.TextAddComments
            ssheet.Cells(nr, 18) = Me.DTPickerInvoiceDate
            ssheet.Cells(nr, 19) = Me.TextInvoiceNo
            
            
        MSG2 = MsgBox("Do you want to input new field?", vbYesNo)
        If MSG2 = vbYes Then
        Unload Me
        Worksheets("Data Base").Activate
        userform1.Hide
        userform1.Show
        Else
        Unload Me
        End If


    Else
       Unload Me
       Worksheets("Data Base").Activate
       userform1.Hide
       userform1.Show
     End If

 Application.Calculation = True
End Sub



Private Sub CommandButton2_Click()
 Unload Me
End Sub

Private Sub CommandButton3_Click()
Unload Me
Worksheets("Data Base").Activate
userform1.Hide
userform1.Show
End Sub

Sub Open_Form()
    Worksheets("Data Base").Activate
   
    userform1.Show
    
    
End Sub

It is a data entry form which gives the user options to reset the form, submit or reload it. I it works fine at same time but as long as I submit for first time and reload it to submit next item it gives me that error.

I checked it on another pc and it works fine, I am not sure what is wrong with my pc.

[jaslake]

Hi siroos12

With Code changes in the attached I get no indication of this

as long as I submit for first time and reload it to submit next item it gives me that error

Let me know of issues.

[siroos12]

Thanks jaslake,

I see the deferences. I just opened the file and it worked first time that I open the form. on second time when I try to open combo boxes it just close the excel. now I considered if i close the excel file and re open it it does not work even on first time.

[jaslake]

Hi siroos12

I'm not able to duplicate this on the Sample File. Are you getting this behavior on the Sample File or your Live File?

on second time when I try to open combo boxes it just close the excel. now I considered if i close the excel file and re open it it does not work even on first time.

[siroos12]

Hi there,

I just downloaded your file and try to test it.

[siroos12]

Thanks my friend,

I am building this for a customer, lets hope that they use 2007 or 2010. By the way i just removed the part of cod which unload and show the form again after submitting first record. It works now but i need to press the button to add next record which is not too bad

I do appreciate all others if they can help.

Thanks.

[jaslake]

You're welcome...sorry I couldn't help...best of luck.

[Norie]

What happens if you remove the DTPicker controls?

Also, have you tried populating the comboboxes in the Initialize event rather than using the RowSource property?

For example.

    With Worksheets("Ranges")
        Me.ComboRepName.RowSource = ""
        Me.ComboRepName.List = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value
        Me.ComboVehicleMake.RowSource = ""
        Me.ComboVehicleMake.List = .Range("C2", .Range("C" & Rows.Count).End(xlUp)).Value
        Me.ComboColour.RowSource = ""
        Me.ComboColour.List = .Range("E2", .Range("E" & Rows.Count).End(xlUp)).Value
        Me.ComboDealer.RowSource = ""
        Me.ComboDealer.List = .Range("G2", .Range("G" & Rows.Count).End(xlUp)).Value
        Me.ComboAdvert.RowSource = ""
        Me.ComboAdvert.List = .Range("I2", .Range("I" & Rows.Count).End(xlUp)).Value
    End With

[siroos12]

Hi Norie.

I will give it try but i do not know how to initialize the comboboxes. i need to google it

[Norie]

The code I posted is for populating the comboboxes.

It would go in the Initialize event of the userform.

[siroos12]

Dear Norie,

Is it going to be like this?

Option Explicit
Private Sub CommandButton1_Click()
    Dim MSG1 As String
    Dim nr As Long
    Dim ssheet As Worksheet

    If Me.TextStockNo = "" Then
        MsgBox "Form incomplete"
        Exit Sub
    End If
    
  
    '    Application.Calculation = False
    Set ssheet = ThisWorkbook.Sheets("Data Base")
    MSG1 = MsgBox("Do you want to submit the form?", vbYesNo)
    If MSG1 = vbYes Then
   
      With Worksheets("Ranges")
        Me.ComboRepName.RowSource = ""
        Me.ComboRepName.List = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value
        Me.ComboVehicleMake.RowSource = ""
        Me.ComboVehicleMake.List = .Range("C2", .Range("C" & Rows.Count).End(xlUp)).Value
        Me.ComboColour.RowSource = ""
        Me.ComboColour.List = .Range("E2", .Range("E" & Rows.Count).End(xlUp)).Value
        Me.ComboDealer.RowSource = ""
        Me.ComboDealer.List = .Range("G2", .Range("G" & Rows.Count).End(xlUp)).Value
        Me.ComboAdvert.RowSource = ""
        Me.ComboAdvert.List = .Range("I2", .Range("I" & Rows.Count).End(xlUp)).Value
    End With
        With ssheet
            nr = .Cells(.Rows.Count, 2).End(xlUp).Row + 1
            .Cells(nr, 2) = Me.TextStockNo
            .Cells(nr, 3) = Me.ComboRepName
            .Cells(nr, 5) = Me.DTPickerDate
            .Cells(nr, 6) = Me.TextYearModel
            .Cells(nr, 7) = Me.ComboVehicleMake
            .Cells(nr, 8) = Me.TextDescription
            .Cells(nr, 9) = Me.TextRegNo
            .Cells(nr, 9) = Me.TextMileage
            .Cells(nr, 11) = Me.ComboColour
            .Cells(nr, 12) = Me.TextBought
            .Cells(nr, 13) = Me.TextSold
            .Cells(nr, 15) = Me.ComboDealer
            .Cells(nr, 16) = Me.ComboAdvert
            .Cells(nr, 17) = Me.TextAddComments
            .Cells(nr, 18) = Me.DTPickerInvoiceDate
            .Cells(nr, 19) = Me.TextInvoiceNo
        End With
    End If
    Call UserForm_Initialize

    '    Application.Calculation = True
End Sub

[Norie]

No, the code goes in the form's Initialize event.

Private Sub UserForm_Initialize()

   With Worksheets("Ranges")
        Me.ComboRepName.RowSource = ""
        Me.ComboRepName.List = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value
        Me.ComboVehicleMake.RowSource = ""
        Me.ComboVehicleMake.List = .Range("C2", .Range("C" & Rows.Count).End(xlUp)).Value
        Me.ComboColour.RowSource = ""
        Me.ComboColour.List = .Range("E2", .Range("E" & Rows.Count).End(xlUp)).Value
        Me.ComboDealer.RowSource = ""
        Me.ComboDealer.List = .Range("G2", .Range("G" & Rows.Count).End(xlUp)).Value
        Me.ComboAdvert.RowSource = ""
        Me.ComboAdvert.List = .Range("I2", .Range("I" & Rows.Count).End(xlUp)).Value
    End With

End Sub

By the way, have you tried removing the DTPickers? That was kind of the main point of my post.

[siroos12]

yes i did. but does not help. let me try this code and will update you.

[siroos12]

Thanks Norie, it does not give me that error any more but new issue. the "reset form" button does not work any more. and I need a message box popup after submitting the form says: "Do you want to submit new record?" with yes/no buttons.so by pressing submit button msgbox says "do you want to submit?". by pressing "yes" I need the next msgbox "Do you want to submit new record?" popup and by pressing "yes" reset the form and by pressing "no" close the form. how do I do that?

[Norie]

What I posted shouldn't affect anything else.

Did you remove/change any of the existing code?

[siroos12]

Wow it works now, it was my mistake! i added your code to the file that "jaslake" provided. I just added it to my initial file and it works stunning. Thanks a lot guys.

[Winon]

Hello siroos12,

Jaslake and Norie have gone to great lengths helping you to solve your issue.

Please be so polite as to at least mark this Thread as Solved.

Thank you.

Regards.

[siroos12]

Hi Winon,

I really do appreciate of their help. I did not want to be impolite my friend but if I did something wrong I am sorry for that and thank a lot for their help and your comment as well. thanks all.

I do not know how to mark it solved? I wanted to do it before but i did not know how to?!
Would you please tell me how to do that?

Best regards.

[Winon]

Hello siroos12,

That is the way to go, thank you for your feedback.

Go to each Post of everyone that has offered you a solution, and click on the Star * to the far left at the bottom of that Post, and add to their Reputation.

Then go to your first Post, and above the Post Screen, select Thread Tools> Solved.

Thank you kindly.

Regards.

[siroos12]

I will do it now, and will do on my other post later as well. thanks a lot.

[Winon]

Hello siroos12,

Thank you for your kind co-operation.

Merry Christmas to you, and all you nearest and dearest.

Regards