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.



On Jun 27, 10:52 am, Tom Ogilvy
wrote:
I would try using the Click Event rather than the Change event. Just
something to try.

--
Regards,
Tom Ogilvy



"Ken" wrote:
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, befo=j
Nodupes.Add Swap2, befo=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- Hide quoted text -

- Show quoted text -