1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Discussion in 'VBA Macros' started by mdavid, Nov 8, 2018.

  1. mdavid

    mdavid Member

    Messages:
    64
    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
  2. Marc L

    Marc L Excel Ninja

    Messages:
    4,257

    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 …
    YasserKhalil likes this.
  3. mdavid

    mdavid Member

    Messages:
    64
    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 (vb):
     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
  4. mdavid

    mdavid Member

    Messages:
    64
    Hi, please can you explain how I activate debug when I double-click on a cell
    which should cause a userform to open
    Code (vb):

    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: Nov 9, 2018
  5. mdavid

    mdavid Member

    Messages:
    64
    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: Nov 10, 2018
  6. mdavid

    mdavid Member

    Messages:
    64
    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

Share This Page