Assigning Keyboard Command to "Hide"

[Mark Aalyson]

The word "Hide" appears at least 4 times as a menu item in Excel 2004. Three
times under Format (row, column, sheet) and again under Window. I'm getting
quirky results while attempting to assign a keyboard command to any of
these, sometimes getting a hidden row when I want to hide a column, and so
on. I'm typing >Hide< in the system preference pane. Should I be typing
something else to differentiate them?

 

[jpdphd]

The word "Hide" appears at least 4 times as a menu item in Excel 2004. Three
times under Format (row, column, sheet) and again under Window. I'm getting
quirky results while attempting to assign a keyboard command to any of
these, sometimes getting a hidden row when I want to hide a column, and so
on. I'm typing >Hide< in the system preference pane. Should I be typing
something else to differentiate them?

Mark
I think you have to redefine the submenu name(s) first. In Excel
Tools > Customize Toolbars/Menus...
A new toolbar appears with all of the menus in rectangular boxes. Find
the one you want to change (eg, Format > Row > Hide). Double click
"Hide" and you have the opportunity to change it to "Hide Row" or
whatever you want. At this point, you could also assign a keyboard
shortcut.
jpdphd

 

[JE McGimpsey]

The word "Hide" appears at least 4 times as a menu item in Excel 2004. Three
times under Format (row, column, sheet) and again under Window. I'm getting
quirky results while attempting to assign a keyboard command to any of
these, sometimes getting a hidden row when I want to hide a column, and so
on. I'm typing >Hide< in the system preference pane. Should I be typing
something else to differentiate them?

The system keyboard shortcuts can be rather a blunt instrument - looking
for the word of the command rather than any specific contextual
identifier.

You can assign the shortcut(s) in XL with specificity. Choose
Tools/Customize/Customize Keyboard... to find command(s) within their
individual menus/submenus, and assign the keyboard shortcut(s)
accordingly.

 

[Mark Aalyson]

I had tried this tactic and that suggested by JE McGimpsey but curiously the
Hide command does not appear among the list of menu items that can be
customized.

 

[JE McGimpsey]

I had tried this tactic and that suggested by JE McGimpsey but curiously the
Hide command does not appear among the list of menu items that can be
customized.

Hmmm...

Well, there is one (Window/Hide).

I set my keyboard shortcuts via macro, and would have sworn that the
Format/Row/Hide and Format/Column/Hide commands were available as well.
Seems I was incorrect.

As a workaround, one can easily set up one's Personal Macro Workbook (or
another add-in or file in the the Startup menu) to provide this
functionality.

In the Personal Macro Workbook's ThisWorkbook code module:

Private Sub Workbook_Open()
HideShortcuts
End Sub

In a regular code module (Insert/Module):

Public Sub HideShortcuts()
With Application
' *+h ===> CMD-SHIFT-h
.OnKey Key:="*+h", Procedure:="HideRow"

' *%h ===> CMD-OPT-h
.OnKey Key:="*%h", Procedure:="HideCol"
End With
End Sub

Public Sub HideRow()
'Format/Rows/Hide control ID = 883
CommandBars.FindControl(Id:=883).Execute
End Sub

Public Sub HideCol()
'Format/Column/Hide control ID = 886
CommandBars.FindControl(Id:=886).Execute
End Sub

See the OnKey method in VBA/Help for more potential key combinations.

 

[jpdphd]

I had tried this tactic and that suggested by JE McGimpsey but curiously the
Hide command does not appear among the list of menu items that can be
customized.

I was tricked by seeing the Keyboard... button after double clicking
"Hide" as I described. As you say, that leads to a dead end. But, if
you rename to "Hide Row" and then use the "system preferences" route,
you should be ok.
jpdphd

 

[Mark Aalyson]

JPD:

Your fix worked, thank you! I have changed my menu names to Hide Column,
Unhide Column, and so on. Works perfectly. I have another keyboard question
which I'll post to a separate topic.