Application.OnKey Ctrl Shift 0

Question

I'm trying to tie macro to a key combinations in Excel 2010 (v14.0).

In the ThisWorkbook code I've:

Option Explicit Private Sub Workbook_Open() Application.OnKey "^+1", "Foo" Application.OnKey "^+0", "Bar" End Sub

In the Module1 code:

Option Explicit Sub foo() MsgBox "hello" End Sub Sub bar() MsgBox "world" End Sub

When I press CTRL+SHIFT+1 Excel says "hello". When I press CTRL+SHIFT+0 Excel does not say "world".

I cannot get Application.OnKey to work with ^+0 for any macro. Is there a way to do this? Why doesn't the code above work?

Doug Glancy · Accepted Answer · 2016-02-06 02:37:30Z

In addition to Jeeped's answer:

Alt-H-O-U-L unhides columns in Excel 2016. C hides them.

And you could substitute the following in Jeeped's code:

Commandbars("Column").Controls("Unhide").Execute.

Don't know that it's any improvement over EntireColumn.Hidden = False, but what the heck.

user4039065 · Accepted Answer · 2016-02-06 01:22:03Z

You are equating the built-in menu commands with custom sub procedures. They are not interchangeable with the Application.OnKey method but a custom macro can easily be created that duplicates the built-in command's functionality.

In a module code sheet:

Sub myUnHideColumns() With Selection .EntireColumn.Hidden = False 'alternate toggle visible/hidden '.EntireColumn.Hidden = Not .EntireColumn.Hidden End With End Sub Sub myHotKeys() Application.OnKey "^+0", "myUnHideColumns" End Sub

In your case you would want to move the affecting code from myHotKeys to your Workbook_Open event macro.

You completely ignored my question and instructed me to do something that doesn't work. "Is there a trick to Ctrl+Shift+0? Is it completely unavailable? Why?"
Sorry to hear that you were not able to implement this. I do not post untested code. Good luck with your project.
I'd be very interested to know about your system configuration, because I implemented exactly what you said on 3 different computers and it worked on none of them.

Collectives™ on Stack Overflow

Application.OnKey Ctrl Shift 0

2 Answers 2

Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Related