CODEWITHSUNDEEP
vbaexcel
Adam Hoelscher
Adam Hoelscher

Reputation: 1892

Application.OnKey Ctrl Shift 0

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?

Upvotes: 1

Views: 1680

Answers (2)

Doug Glancy
Doug Glancy

Reputation: 27478

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.

Upvotes: 1

user4039065
user4039065

Reputation:

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.

Upvotes: 0

Related Questions