Blog

Keep up to date with the latest news and ideas from Numeritas and the world of financial and business modelling.

News

Excel 2013 cell context menu index changed

TAGS: VBA, context menu, array, excel version, index

DATE: Fri 14 Oct 2016
BY: Ben Norris

Excel 2013 cell context menu index changed

In Excel 2013 the index of the cell context menu commandbar has changed

Our in-house toolkit makes use of the context menu in Excel - this is the menu that appears when you right-click on a cell in Excel. The toolkit adds a couple of extra menu items that make it quicker for us to work in Excel and tells us some additional information about the selected cells.

This has been working well for a number of years but we've recently started using Excel 2013 more frequently in the office and the extra menu items weren't appearing. Hmmm.

A quick look through the code and a bit of debugging showed that it was trying to build the items and was adding them to the commandbars, but they still weren't appearing.

A bit of head scratching, code inspection and pondering later and I managed to figure it out.

There are actually different context menus that appear depending on what state Excel is in when you right click, and indeed what you right click on. So for example when you right click on a cell in Normal View it shows you one menu (let's say it's index number is 35) but when you right click on the same cell and you're in Page Break Preview, it shows you a nearly identical context menu but it has a different index (say 38). There are also different menus that get shown when you click on a list (table).

To make it easy to add the toolkit menu items to all of the necessary context menus, I had stored a list of commandbar indexes in an array that the code would loop through.

It turns out that in Excel 2013 the index numbers had changed...

Luckily it was easy to add another array of the new indexes and use the appropriate array depending on which version of Excel is running.

For reference, these are the new (and old) indexes of the context menus we alter:

Cell (normal view) = 37 (new), 35 (old)
Cell (page break preview) = 40 (new), 38 (old)
List range (normal view) = 73 (new), 71 (old)
List range layout (normal view) = 74 (new), 72 (old)
List range layout (page break preview) = 76 (new), 74 (old)

UPDATE:

It looks like in Excel 2016 the index numbers are back to where they were in 2010. Sigh...

Ben Norris BA (Hons), FCA

Ben trained as a Chartered Accountant with KPMG in London specialising in the Financial Sector. After qualifying in 2002, he spent two years working in KPMG’s Corporate Recovery team in the Cayman Islands before returning to London to join their Financial Modelling group.

Have your say

We love to hear what you think. please note that comments are moderated so there might be a slight delay. Your email address will not be published.

Comments

No comments to display, be the first! Leave a comment in the box above.