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
Bookmarks