'object disconnected from client ...'

[Christian Treffler]

Hello,

I'm getting the "Object disconnected from client..' error message in my
VBA project (Excel 2003 on Windows XP professional, all latest patches
installed).

First, here's an excerpt of the code:

Sub CheckPT()
Application.Goto Reference:="IFailureModeM"
' This is on Worksheets("Questionnaire")

If Range("IPackageType").Value = _
Worksheets("Supporting Data").Range("LPT_BGA").Value Then

' Omitted, because this section is not executed

Else ' occurs when "LPT_BGA" is empty

With Selection.Validation
.Delete
' ***The error occurs in the next statement***
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=LFM_Empty"
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = False
.ShowError = True
End With

End If
End Sub

This sub is called from several other subs without any problem. The
problem occurs, when I open a custom dialog box (userform) with a
checkbox, OK and Cancel button. After clicking OK, the above sub is
called. Due to other actions, the if statement will always perform the
else clause on this occasion. And here the error occurs, but funny
enough it occurs *after* the .Delete statement. The .Delete statement is
executed correctly.

I have checked everything I could find regarding that error message, but
cannot get behind the root cause. It does not seem to be connected to
unqualified property call (At least I cannot make it work with a
qualified call - Or I do not understand the qualified/unqualified issue)

If somebody is interested, below is the code of the sub which opens the
dialog box.

Can anybody help me with that problem?

TIA,
Christian

Public Sub ClearQuestionnaire()

BtnNewDlg.Show

Application.EnableEvents = False
' Dissable change events, because sheet will be changed by
' following subs

' The following actions all take place on
' Worksheets("Questionnaire")
For Each c In Range("HAllData").Cells ' Go through all cells
c.MergeArea.ClearContents ' and delete content
Next c ' in "HAllData"

CheckQuestionnaire
' This sub calles 3 other subs prior to CheckPT() which perform
' actions on Worksheets("Questionnaire").
' Some of these are similar to the one in the failing CheckPT(),
' but they work fine.

End Sub

 

[James Snell]

It's probably because of the delete statement being inside the with and
you're deleting part of the object you're working with. I tend to make it a
rule to never delete objects from inside a with block because there is always
a possibility you could be dereferencing the object you're "with".

Try moving it outside the With. Like this...

Selection.Validation.Delete
With Selection.Validation
' ***The error occurs in the next statement***
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=LFM_Empty"
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = False
.ShowError = True
End With

Theoretically you'd expect it to still behave the same, but I'm willing to
bet that once compiled to p_code all the instructions in the With block are
executed together as a block, leading to the delete and the add tripping each
other up. Also I'd lay odds that the code works ok when you step through
that section.

 

[Christian Treffler]

Hi James,

thanks for your reply.

It's probably because of the delete statement being inside the with and
you're deleting part of the object you're working with.

I tried it with the following changes:

Selection.Validation.Delete
With Selection.Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=LFM_Empty"
<snip>

It didn't change anything: Same error message. I guess that the delete
method of a validation object does only delete the validation
conditions, not the object itself.
That's the reason why the sub worked before.

Now I came back from lunch, started the computer again, and ...
.... get the same error message within all other subs which change
validation of a range.

I did some tests and found that the problem is probably related to the
fact that I'm referring to merged cells.

Here's a little sample code:

Application.Goto Reference:=Range("IPLDetail")
MsgBox Range("IPLDetail").Addres ' Shows "$R$33"
MsgBox Selection.Address ' Shows "$R$33:$U$33" (merged cells)
MsgBox Range("IPLDetail").Text ' Shows the content of the merged cell
MsgBox Selection.Text ' Runtime error 94
' Invalid use of Null


So I exchanged every "Selection.Validation" with
"Range("<name>").Validation", and it works - xxcept that sub CheckPT()
which gives me the following error:

Run-time error '-2147417848 (80010108)':
Method 'Add' of object 'Validation' failed.

The code '-2147417848 (80010108)' is identical to the previous error
'object disconnected from client ...'.

I'm still scratching my head over that one.

CU,
Christian

 

[Christian Treffler]

Hello,

I'm getting the "Object disconnected from client..' error message [...]
This sub is called from several other subs without any problem. The
problem occurs, when I open a custom dialog box (userform) with a
checkbox, OK and Cancel button.

I did a lot of searching on the web to find a solution. The only thing I
did find was that other people were having the same problem. But one of
them pointed me in the right direction: The problem occurred only, when
he started the sub from a button_click event.

That's the same for me: The userform is opened by clicking on a button.
When clicked, the button get's the focus and still has it, when my sub
is called.

If I set the TakeFocusOnClick property of the button to False, the
problem disappears.

Right now I'm searching for a way to move the focus back to the
worksheet, when the button is clicked. I don't want to rely on that
property setting.

CU,
Christian