object invoked is disconnected from its clients

[Ken]

I started getting this annoying error

Automoation error
The object invoked has disconnected from its clients

A little newsgroup research showed me that it could be related to not
fully qualifying an object or extra large code modules. The module is
quite small, Bob Bovey's Code Cleaner, which works miracles sometimes
did not fix it. I fully qualified all the references and it worked
okay. That did not make much sense, since I often fail to fully
qualify things, often leading to problems, but never this one until
last night.

When I add the MSGBOX line below, I get the error again. As described
by many others in earlier posts on this newsgroup, the error happens
only the second time the code is run.

Private Sub ComboBox1_Change()

Dim R As Range
Dim Nodupes As New Collection
Dim AllCells As Range, Cell As Range
Dim i As Integer, j As Integer
Dim Swap1, Swap2, Item

Set AllCells = Sheets("Jobs").Range("c1:c532")

Selected_Job = WT.ComboBox1.Value

For i = Nodupes.Count To 1 Step -1
Nodupes.Remove i
Next i

------Below is the additional line that causes the problem-----

MsgBox Selected_Job

On Error Resume Next

For i = 1 To AllCells.Rows.Count
If Sheets("Jobs").Cells(i, 1).Value = Selected_Job Then
Nodupes.Add Sheets("Jobs").Cells(i, 3).Value, CStr(Cell.Value)
Next i

On Error GoTo 0

For i = 1 To Nodupes.Count - 1
For j = i + 1 To Nodupes.Count
If Nodupes(i) > Nodupes(j) Then
Swap1 = Nodupes(i)
Swap2 = Nodupes(j)
Nodupes.Add Swap1, before:=j
Nodupes.Add Swap2, before:=i
Nodupes.Remove i + 1
Nodupes.Remove j + 1
End If
Next j
Next i

WT.ComboBox2.Clear

' Add the sorted, non-duplicated items to a ListBox

For Each Item In Nodupes
WT.ComboBox2.AddItem Item
Next Item

WT.ComboBox2.DropDown

End Sub

Does this make any sense to anyone? "Selected_job" is a module level
variable. I added the Msgbox line to assist in trouble shooting since
combobox2 was not picking up the data I expected. Without the msgbox
line i could run it over and over without any fatal errors, just
without picking up any items. All the code is related to a userform
named WT.

Thanks

Ken

 

[Tom Ogilvy]

I would try using the Click Event rather than the Change event. Just
something to try.

 

[Ken]

Thanks Tom. I am now using the click event but I am still getting the
error pretty often. This is a particularly annoying error since it
necessitates closing Excel with the task manager and restarting.

 

[Tom Ogilvy]

Nothing extraordinary about the code you show (John Walkenbach's). I have
used it many times without problem. That error isn't associated with only
one well known cause, so I can't say.

 

[Ken]

Tom
I found I had two references to the Excel 11 Object Library. I am not
sure how that happened. I was optimistic about that being the problem,
but, it still happens sometimes now that I have eliminated one. I
also went with your suggestion about the click event, which may have
helped some. I am pretty sure that the Walkenbach code (which he
credits to a J.G. Hussey) has always worked without any problems
before. This code the most completely qualified codes I have ever
developed (of course it wasn't that way initially) and the code is
also quite small, so I am a bit perplexed as to why I would be
gettting that error. Anyway, thanks for taking the time to consider
this problem, and the million others that have been extremely helpful.
ken