Keyboard shortcut to Name Box

[Gregory]

Just below the main toolbar is the Formula Bar.. and to the left of
that is the Name Box. One can select and name a Cell RANGE by typing
the cell-limits in the Name Box such as B2:Z52 (i.e. upper left :
lower right)

The question is.. what's the shortcut (hotkey) to engage the Name Box?
so that a range can be immediately typed in?

-Gregory

 

[Gord Dibben]

Gregory

F5 key will do the trick.


Gord Dibben MS Excel MVP

 

[Gregory]

Gregory

F5 key will do the trick.

Gord Dibben MS Excel MVP

Yep.. that works. I dumped the HELP [F1] `shortcut keys´ window to
printer and it was 20 pages!!

Additionally.. what's the hotkey to: Return to worksheet tab #1? CTRL
+Home will go to cell A1.


-Gregory

 

[Gord Dibben]

CTRL + PageUp/Pagedown will cycle through the sheets.

No way to go to first worksheet without using VBA or hyperlinks.

Not a shortcut key but if you right-click on the navigation arrows at bottom
left you can select from a list of sheets.


Gord Dibben MS Excel MVP

Gregory

F5 key will do the trick.

Gord Dibben MS Excel MVP

Yep.. that works. I dumped the HELP [F1] `shortcut keys´ window to
printer and it was 20 pages!!

Additionally.. what's the hotkey to: Return to worksheet tab #1? CTRL
+Home will go to cell A1.


-Gregory

- Show quoted text -

 

[Chip Pearson]

F5 key will do the trick.

That works as long as you are using names only as the destination for GoTo,
which is all the OP wanted to do. If you need to change a name, you've got
to CTRL+F3, which is, at least to me, an ergonomically uncomfortable key
combo when done with one hand.

I use a keyboard shortcut, CTRL+SHIFT+N, to set focus to the name box in
order to either go to a range name or create a new range name. One key
combo, multi-use.

Public Declare Function SetFocus Lib "user32" (ByVal hwnd As Long) As Long

Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Public Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, _
ByVal lpsz1 As String, ByVal lpsz2 As String) As Long

Sub SetFocusNameBox()
Application.OnKey "^+N", ThisWorkbook.Name & "!FocusNameBox"
End Sub

Sub FocusNameBox()
Dim Res As Long
Res = SetFocus( _
FindWindowEx( _
FindWindowEx( _
FindWindow("XLMAIN", vbNullString) _
, 0, "EXCEL;", vbNullString) _
, 0, "combobox", vbNullString))

End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Gord Dibben]

Thanks Chip

Missed the "name" part in

I took it OP just wanted to type in range address for selection which can be
done in the F5 dialog box.


Gord

 

[Gregory]

That's what I really wanted: CTRL+SHIFT+N but it brings up Name
Manager 4.0 )

Nowever, working with it more.. the object was to bring up the Name
Box (without any names) and type in a range of cells.. in this case
B2 : CX74 (spaces removed, of course) and that will select a BIG
range of cells too difficult to drag the mouse across. Plus they're
ALL the same size selections. Then, after the RANGE is selected, a
NAME can be typed into the Name Box. Need to play with it more.

Thanks to everyone for the great tips. Be careful when dumping hotkey
list to printer!!

-Gregory

 

[Andy]

This works (thanks!), But is there something easier, like the one-line
macro I use to set focus to the font box?:

Application.CommandBars("Formatting").FindControl(ID:=1728).SetFocus

 

[Chip Pearson]

Neither the NameBox nor the Formula Bar is part of the CommandBars object
family. Therefore, you can't get a reference to either that can be used with
SetFocus.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Andy]

Neither the NameBox nor the Formula Bar is part of the CommandBars object

family. Therefore, you can't get a reference to either that can be used with
SetFocus.

Which explains my lack of success trying to figure out why nothing I tried
worked (and the flat spot on my forehead).

I was about to kludge a macro using AutoHotkey or Macro Express when I saw this
post. So I thank you again!

 

[Gregory]

You programmer types are amazing. )

This has been a good discussion, with many slick ways to select a
RANGE of cells. One guy asked some years ago "how do you know when the
END of the cell-range is coming so as to avoid mouse, or `cell select
overshoot?´ And I replied.. watch the scroll-bar sliding button. When
it gets near to the END of the scroll track range.. then SLOW DOWN
with the mouse!! { chuckle }

-Gregory