Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
object invoked is disconnected from its clients
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
object invoked is disconnected from its clients
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
object invoked is disconnected from its clients
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 - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
object invoked is disconnected from its clients
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. -- regards, Tom Ogilvy "Ken" wrote: 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 - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
object invoked is disconnected from its clients
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 On Jun 27, 1:26 pm, Tom Ogilvy wrote: 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. -- regards, Tom Ogilvy "Ken" wrote: 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 -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Object invoked disconnected from its clients. | Excel Programming | |||
Automation Error : The Object Invoked Has Disconnected From Its Clients !! | Excel Programming | |||
Automation Error: The Object Invoked Has Disconnected from Its Clients (Excel) | Excel Programming | |||
Automation Error: The Object Invoked Has Disconnected from Its Clients | Excel Programming |