Results 1 to 15 of 15
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This is a bit of an odd one, no major issue, but it's been bugging me for a while.

    I have a workbook that contains a wb_open event that works 99% of the time. I'll call it wb1

    However I have another workbook (wb2) that is very often open with a specific sheet selected when I (and others) open wb1. When this happens, wb1's open event refuses to fire.

    This has been going on for some time, and I have established that this is not user, computer, or workbook specific. This ONLY occurs when a certain sheet is active in wb2, and wb1 is opened.

    There is nothing special about the sheet in wb2 that causes the problem. It contains data that was created using code, no formula's. If i select another sheet in wb2 and open wb1, everything works ok.

    What gives?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Do you have a line

    Application.EnableEvents = False

    anywhere in your code?

  3. #3

  4. #4
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='VegasNath' post='783225' date='06-Jul-2009 11:18']No, in neither w/b.[/quote]

    Can you post stripped down copies of the offending files and specifics as to which sheet is the problem?
    Regards
    Don

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    Are both workbooks on the same drive/local PC or across a network?
    Jerry

  6. #6
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='Jezza' post='783231' date='06-Jul-2009 16:49']Are both workbooks on the same drive/local PC or across a network?[/quote]

    They are both on the same drive.

    Don: Here ya go:

    If you open wb1, stay on summary tab, then open wb2, open event will execute.

    Close both

    Now open wb1, select 65-20's or 75-20's tab, open wb2, open event fails to fire



    [attachment=84572:wb1.xls][attachment=84573:wb2.xls]

    PS: wb2 is not my actual live wb, this is just a blank file that I created and added an open event, to demonstrate the problem, which proves that the issue is with specific sheets in wb1.
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I must admit that I don't have the slightest idea why, but the problem is that more than one cell is selected on those sheets. If you select a single cell, then open wb2.xls, its Workbook_Open event procedure will run correctly.

  8. #8
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='783257' date='06-Jul-2009 22:04']I must admit that I don't have the slightest idea why, but the problem is that more than one cell is selected on those sheets. If you select a single cell, then open wb2.xls, its Workbook_Open event procedure will run correctly.[/quote]

    Whoa! How odd is that????, and yes, there will always be multiple cells selected in those sheets, the code does that ready to be pasted into wb2.

    Have you ever come accross such nonsense in the past?

    It's not a problem, just an annoyance, nice to know the cause (Thanks), it would be nice to know why? Anyone?

  9. #9
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I just noticed:

    If I select a load of cells in a new blank workbook, and open wb2, the open_event runs ok. So there must be something wrong in the wb1 file itself, and only dislikes multiple selected range's in that wb. Strange!

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    It has to do with the filter highlighting: the column headers have conditional formatting using the custom function FilterOn.

    It turns out that this function is called many times whenever focus shifts to or from the worksheet, and the number of calls is proportional to the number of selected cells in the filtered range. Why exactly this disables the Workbook_Open event of other workbooks I don't know, but somehow it does.

    I had never seen this side effect of filter highlighting, and I don't know how to avoid it, so for the moment, I can only recommend that you turn off filter highlighting, i.e. remove the conditional formatting from the column headers.

  11. #11
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I can do that easily enough and without it being an issue.

    Thanks very much for spending time looking at this.

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Having had a look at wb1, I would strongly suggest you start rebuilding it. It behaves very strangely indeed on my PC and I suspect that corruption is starting to creep into it.
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Thumbs up

    [quote name='HansV' post='783269' date='06-Jul-2009 17:46']It has to do with the filter highlighting: the column headers have conditional formatting using the custom function FilterOn.

    It turns out that this function is called many times whenever focus shifts to or from the worksheet, and the number of calls is proportional to the number of selected cells in the filtered range. Why exactly this disables the Workbook_Open event of other workbooks I don't know, but somehow it does.

    I had never seen this side effect of filter highlighting, and I don't know how to avoid it, so for the moment, I can only recommend that you turn off filter highlighting, i.e. remove the conditional formatting from the column headers.[/quote]

    Two questions about the HiLite Filters AddIn code:
    1. Can anyone tell me which procedure initiates action -- that is, other than being initiated through the menu?
    2. Can anyone explain why the code disregards a Breakpoint or Stop command?
    Regards
    Don

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Filter highlighting works through conditional formatting based on the FilterOn function.

    Apparently, Excel disables breakpoints when executing a function in conditional formatting; perhaps the event monitoring necessary for that would have a negative effect on performance, but I'm just guessing there.

  15. #15
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Thank you for the insight Hans. [quote name='HansV' post='783350' date='07-Jul-2009 10:06']Filter highlighting works through conditional formatting based on the FilterOn function.

    Apparently, Excel disables breakpoints when executing a function in conditional formatting; perhaps the event monitoring necessary for that would have a negative effect on performance, but I'm just guessing there.[/quote]
    Regards
    Don

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •