VBA Procedure causing problems in Excel 2013. Older Excel versions work fine!

gary_wood_1999

New member
Joined
Jan 11, 2016
Messages
6
Reaction score
0
Points
0
Location
Leeds
Hello

We have fairly recently upgraded to Office 2013. One particular excel workbook we use has 60 plus worksheets and therefore we use a simple VBA popup menu created by clicking on buttons at the top of each sheet to ease navigation rather than scrolling through all the Sheet Tabs at the bottom of the workbook.

This macro has always worked fine on our older versions of Excel. In Excel 2013, while the macro still executes and takes you to the desired page, after running the macro you can no longer close the workbook by going up to the top right corner and clicking the X. In addition you cannot scroll down the sheet using the wheel on the mouse (although you can by dragging the scroll bar down on the right hand side

If you do not use the macro, and manually click on the Sheet Tabs, everything works fine. Similarly if you run the Macro ("CreateDisplayPopUpMenuCourse" in the attached example file) from the developer tab rather than clicking on the button, it works without an issue. I found a post regarding a similar issue which said it was something to do with userforms being Modal or Modeless but I haven't being able to find a solution to this.

I have created a very simple example to demonstrate the issue. Please note that the problem only exists in Excel 2013.

If anyone has any solutions to this problem I would be very grateful.

Many thanks,

Gary
 

Attachments

  • Example.xlsm
    30.2 KB · Views: 21
You don't need any VBA to do this. It's builtin in Excel. See below
Left down corner navigationicons, right click, select sheetname


attachment.php
 

Attachments

  • __switch.jpg
    __switch.jpg
    51.3 KB · Views: 93
Last edited:
Thank you for your reply. I appreciate that excel does have that functionality however i think my example is somewhat simplistic compared to the actual file. Within the file i'm trying to fix there are about 60 worksheets split into a number of categories and some of those have sub menus within them (screenshot attached). This file is used by around 100 people for nearly 3 years and so i really don't want to change the way people navigate through it.

I've worked through every excel problem I have some across but this one has really stumped me. If you or anyone else have any further ideas then i'd really appreciate it.

Thanks
 

Attachments

  • Screenshot.jpg
    Screenshot.jpg
    88.9 KB · Views: 26
I see....

Maybe the attachment does help.
 

Attachments

  • __Example_snb.xlsm
    28.3 KB · Views: 26
Strangely Sheet 2 seems to work but if you select sheets 1, 3 or 4 using the button, you still can't scroll. If you're on a sheet that isn't scrolling, you can't close the file either. It's bizarre!
 
I further reduced/simplified the code.
Maybe it has some beneficial influence.
 

Attachments

  • __Example_snb.xlsm
    27.8 KB · Views: 14
Thank you for your continued efforts with this however it still has the same problem. I think it's something to do with the menu not shutting down properly. It's bizarre!
 
Maybe activating the sheet could be of influence:

Code:
Sub M_goto()
  thisworkbook.sheets(Application.Caller(1).activate
  Application.Goto thisworkbook.Sheets(Application.Caller(1)).Cells(1),true
End Sub
 
Still not working i'm afraid. I also tried to use .activate instead of .select on other part of the code but nothing seems to solve it.
 
Probably you are forced to use the XML of the ribbon.
 
Back
Top