+ Reply to Thread
Results 1 to 8 of 8

Excel 2013 confused re active cell, worksheet etc after CommandBars...ShowPopups user pick

  1. #1
    Registered User
    Join Date
    01-12-2012
    Location
    Bristol, England
    MS-Off Ver
    2003, 2007, 2010 & 2013
    Posts
    20

    Excel 2013 confused re active cell, worksheet etc after CommandBars...ShowPopups user pick

    Hi all. I am at my wits end over an apparent Excel 2013 application-level bug, for which I cannot find any VB workaround.

    In the attached sample workbook which I cobbled together to demonstrate the problems, Sheets 1 and 2 have right-click popup menus offering a single user option to move to the other worksheet. If you open the workbook in Excel 2013 and pick this right-click option, then play about a bit on the destination worksheet by using Ctrl+Home to try and get to the frozen panes position, and Ctrl+End to get to the last cell in the used range etc, it becomes clear that the 2013 application still thinks it is on the original worksheet, after you have chosen the popup option. You can restore normal behavior after it gets confused, by manually selecting a different worksheet and then returning to the destination one - but doing this in the VB has no effect.

    2013 is not just getting frozen panes wrong in such instances. In this sample workbook I created a single, 1-cell named range in cell J1 of Sheet1, called "Sheet1Rng". If you start on that sheet and use the right-click popup option "Go to Sheet2", then hit Ctrl+Home once you are there, you will notice that as well as cell J1 being erroneously selected as where Excel thinks the panes are frozen to, the "Sheet1Rng" name now appears to the left of the fx formula toolbar, apparently saying that this range is now on Sheet2. Of course it isn't.

    Adding a 'transition' selection of Sheet3 to each right-click-activated procedure's VB to go to the other of sheets 1 or 2, has no effect on this confusion. I have left lines in the VB that do this, and they run in the attached but accomplish nothing.


    There are also other similar issues that crop up when popups are used in 2013, around which cell is active. I was able to find a workaround for them in the VB, as you will find commented out in the attached, in the Sheet1 and Sheet2 Worksheet_BeforeRightClick events. But VB for moving between sheets doesn't respond to anything similar.


    This behavior does not take place in any of the earlier versions of Excel: everything in this sample works just fine in them, so it's 2013-specific. I cannot find any other reports of this in the various forums, or anywhere else - but I am sure other people must have come across it by now.

    Has anyone else seen this? Does anyone know how I can properly address this in the VB?

    The only other alternative I can see would be to instruct all users to 'manually' select some other sheet etc, after running any popup menu pick procedure. That seems like a copout though, and in reality it's unacceptable, as it will quickly leave them more frustrated than I am at this point. What have Microsoft done?

    Many thanks,
    Nelson

  2. #2
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Excel 2013 confused re active cell, worksheet etc after CommandBars...ShowPopups user

    Also behaves same way in Excel 2016
    But if you insert hyperlink to link to sheet2 the problem goes away - see attached. It's a workaround.

  3. #3
    Registered User
    Join Date
    01-12-2012
    Location
    Bristol, England
    MS-Off Ver
    2003, 2007, 2010 & 2013
    Posts
    20

    Re: Excel 2013 confused re active cell, worksheet etc after CommandBars...ShowPopups user

    Hi Kevin. Thanks for your suggestion. It does work, though I may not be able to apply it for the circumstances needed. It's good to know about 2016 also doing this, for the future.

    - Nelson

  4. #4
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Excel 2013 confused re active cell, worksheet etc after CommandBars...ShowPopups user

    Perhaps you could use it in combination with some VBA to achieve almost the same thing.
    What exactly are you trying to achieve? at the moment all it seems to be doing is taking user to another sheet and the VBA for that is
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    01-12-2012
    Location
    Bristol, England
    MS-Off Ver
    2003, 2007, 2010 & 2013
    Posts
    20

    Re: Excel 2013 confused re active cell, worksheet etc after CommandBars...ShowPopups user

    Hi Kevin. Yes, this is just to show the fundamental issue. In the tool I am upgrading, there are many procedures offered to the user via right-click options, different ones on different worksheets, also depending on what cells they have selected. Some of these procedures move to another sheet, and that is where the troubles come in.

    Do you know whether using a form with radio button options, instead of a popup menu, is likely to also show the same unwanted behaviors in 2013?

    - Nelson

  6. #6
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Excel 2013 confused re active cell, worksheet etc after CommandBars...ShowPopups user

    I have not tested radio buttons.

    But when your code was inserted inside
    Please Login or Register  to view this content.
    all worked as it should.

    The problem you identified would appear to only occur when your code is inserted inside
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    01-12-2012
    Location
    Bristol, England
    MS-Off Ver
    2003, 2007, 2010 & 2013
    Posts
    20

    Re: Excel 2013 confused re active cell, worksheet etc after CommandBars...ShowPopups user

    Hi Kevin. That would make some sense. The difficulty is that I need to present options to the user, one way or another. I tried substituting a form in place of the BeforeRightClick menus from my example, and 2013 behaved in exactly the same flaky way.

  8. #8
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Excel 2013 confused re active cell, worksheet etc after CommandBars...ShowPopups user

    This should solve your problem. I have tested it on Excel2016 and it works and Sheet2 {CTRL}{END] took cursor to correct cell.

    Method
    Re-assign one of the function keys to call up the menu instead of right-click.It only reassigns for the current file.

    Notes
    In the macro I have re-assigned {F12} . Try it first as suggested to prove it works, then modify to your preferred key.
    If you prefer to not to use function keys then PageDown = {Pgdn}, PageUp = {Pgup}, End = {End} .....etc

    Do the following and after re-opening the file, hit {F12} when in sheet1
    Put this code in an ordinary module
    Please Login or Register  to view this content.
    Put this code in the ThisWorkbook module
    Please Login or Register  to view this content.
    Then save the file, close it and open it again (triggers when workbook opens)
    Last edited by Kevin#; 03-23-2016 at 08:35 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 06-19-2015, 01:58 PM
  2. Trouble Enabling Active X Controls In Excel 2013
    By mike grottola in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-04-2014, 04:06 PM
  3. Replies: 7
    Last Post: 09-12-2013, 03:54 PM
  4. Macro to copy a value from a table (manually pick) and paste to active cell.
    By cyroselane in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-14-2013, 03:58 AM
  5. pick data from a specific row/column (eg 10/B) related to active cell
    By zonino in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-03-2009, 04:01 PM
  6. Can Excel pick up user name?
    By gjcase in forum Excel General
    Replies: 3
    Last Post: 11-15-2007, 12:36 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1