Jul 05 2020 08:08 PM
Hi all,
Pulling my hair out here. Hope someone can help.
Have recently upgraded computer, OS and Excel versions (Win10, Excel 365 (1902)).
When I try to run one of my personal macros that has a keyboard shortcut it doesn't work. A couple of mins of Googling shows there are now new kb shortcuts in Excel and that I'll have to change the one I had assigned (Ctrl+Q).
Ok, so I assign it to Ctrl+Shift+Q and when I run that something happens but not my macro. So what does that mean ... is it assigned to something else already? Not according to anything I can find online.
So the question is, is there anywhere I can see what shortcuts (including customised ones) are assigned in Excel?
It looks like I can (try to) assign any shortcut under Developer>Macros>Options - for example, Ctrl a - even though it's assigned to something else. So how do I know what shortcut to assign to my macro if I can't see a list of what's already assigned?
Jul 05 2020 08:18 PM
@StewDay , you can view the macros (Alt-F8) and click on the options button to see what shortcut key has been assigned to each macro:
Jul 05 2020 08:42 PM
Jul 05 2020 09:02 PM
@StewDay , I'm sure there's a way to find out programmatically but that's beyond what I can do. Quick web search showed a few results so maybe there's a solution already out there. Here's a couple that looked promising:
Jul 05 2020 10:09 PM
Thanks @TheAntony.
That put me on the trail to something useful ... excel-vba-to-list-key-bindings-onkey
Still didn't allow me to see ALL keyboard shortcuts ... looks like those like Ctrl+x are called OnKey bindings and I didn't work out how to see them.
But there was a reference to using [Add Watch] , a right-click function in the VBA editor to stop any code from executing. After setting that up and using the shortcut Ctrl+Shift+Q I was able to see it was actually assigned to an old macro that had been commented out. So it was running, but there was nothing to run.
I've now re-assigned it to the preferred macro and it's working as expected.
So now if I want to assign a new kb shortcut to a macro I first have to check it's not already used by Excel by referring to a list like this one keyboard-shortcuts-in-excel then turn on [Add Watch] to check it's not already assigned to one of my macros!
Thanks for making things straight forward Microsoft!