• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

The object invoked has disconnected from its clients

mdavid

Member
Hi,
Have the following code:
Code:
Option Explicit
Private Sub Workbook_Open()
ThisWorkbook.Worksheets("Sheet1").Activate
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim lRow As Long
Dim cols As Range
Dim UForm As Object
Dim PrepAdminOPen As Boolean
lRow = Cells(Rows.Count, 2).End(xlUp).Row

 If Not Application.Intersect(Target, Range("F3:F" & lRow)) Is Nothing Then
     Range("F3:F" & lRow).Activate
     Cancel = True
     UserForm1.Show
     Exit Sub
  End If
I get the error: "the object invoked has disconnected from its clients" on UserForm1.show.
The UserForm contains a listbox and close button.
I added the Worksheets and Range .Activate after googling this error, but it didn't help!
Very much appreciate any help in resolving this issue.
Thanks
 

mdavid

Member
Hi,
I have the following code in UserForm1:
Code:
Private Sub UserForm_Initialize()
ListBox1.ListIndex = -1
Me.StartUpPosition = 0
Me.Top = 40
Me.Left = 0.95 * (Application.Left + Application.Width - Me.Width)
End Sub
If I comment out :
Code:
'  Me.Left = 0.95 * (Application.Left + Application.Width - Me.Width)
Then I don't get the error and UserForm1 appears correctly.
 

mdavid

Member
Hi vletm,
I replaced the offending code with:
Code:
Private Sub UserForm_Initialize()
ListBox1.ListIndex = -1
 Me.StartUpPosition = 0
Dim Top As Double, Left As Double
Top = Abs(Application.Top) + _
(Application.Height - ActiveWindow.Height) + _
(Application.UsableHeight - ActiveWindow.UsableHeight)
Left = Abs(Application.Left) + (Application.Width) - (Me.Width + 10)
Me.Top = Top
Me.Left = Left
End Sub
And all's working well.

I didn't comment out Application.Left, Application.Width and Me.Width - as I understand (very little) they are taken from the UserForm object
 

vletm

Excel Ninja
mdavid
I asked to check those values because, if Your original formula gets value as zero, then it will give a strong note.
... now Your newer variation always adds 10 ... then it cannot be zero anymore.
 

mdavid

Member
Hi vletm,
Thanks for this, hopefully I'll remember this when this error occurs next time.
There's very little indication as to what causes "the object invoked has disconnected from its clients " and where to look in the code - finding the solution is just a matter of luck and perseverance for me -bit like winning the lottery!
 
Top