• 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.

excel macro enabled file corrupted

mdavid

Member
Hi,
I have a macro enabled file with userforms, which is behaving strangely and giving errors such as: "the object invoked has disconnected from its clients" - this error crashes the workbook.
When I click on a cell that should open a userform there's just a flash of a white screen but the form doesn't open. The problems usually occur in the same cells, other places in the sheet it works as expected.
I have data in other workbooks that have the same code and userforms and all work ok.
I suspect the data in the sheet has been corrupted and would like to know how I can retrieve and/or repair the data.

Thanks for any help
David
 

Marc L

Excel Ninja

Hi,
this message often has nothing to do with corrupted data
but mostly with a logic error (or lack of controls) within the code.
Use the debug mode to follow the code …
 

mdavid

Member
Hi Mark thanks for this, my problem is I can't find the logic, which made me think that something is corrupted. The errors and behavior is not consistent, when I test it everything works without error nine times out of 10, then suddenly a userform doesn't appear, but there's no error so it's hard to debug, or I get "the object invoked has disconnected from its clients" and click debug I see it's on
Code:
 userform5.Show
Which doesn't tell me very much and I don't know where to look from there.
But it seems you're correct about it not being corruption 'cause I've tried Open>Retrieve data and everything worked ok the 1st few time then it all started again
 

mdavid

Member
Hi, please can you explain how I activate debug when I double-click on a cell
which should cause a userform to open
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   If Not Application.Intersect(Target, Range("I2:I" & lRow)) Is Nothing Then
     Cancel = True
     UserForm1.Show
     Exit Sub
  End If
End Sub
But instead a small rectangle with a down arrow appears bottom right of the cell, instead of UserForm1 showing.
This behavior is limited to only some cells in the column I, other cells when double-clicked show UserForm1, any suggestions what might cause this?
Thanks for any help
David
 
Last edited:

mdavid

Member
Hi,
Here's a couple of screenshots of the problem:

cell-dbl-click1.JPG

Above is correct behavior, double clicking on a cell in column I opens UserForm1.

Below shows what happens when I double click on the cell containing "Nervious system", instead of opening UserForm1 I receive the drop-down icon - which doesn't contain any list

cell-dbl-click2.JPG

If I try to debug Worksheet_BeforeDoubleClick on this cell it doesn't even enter the procedure, double clicking just brings up the drop-down icon.

Any ideas what's the cause of this problem and/or how to correct it?
Thanks for any help
David
 
Last edited:

mdavid

Member
Think I've solved this, for anyone interested
1. Select columns where cells have a drop-down list icon
2. Data > in Data Tools select Data Validation, from the dropdown menu select Data Validation, then under Settings tab click Clear All (bottom left) then OK.

Hope this helps someone
 
Top