Defined Name Box Shortcut

[hamiltonrr]

I got this from Chip Pearson´s site, but don´t works for me (Win XP,
Excel 2003 portuguese).

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()
Dim Res As Long
Res = SetFocus( _
FindWindowEx( _
FindWindowEx( _
FindWindow("XLMAIN", Application.Caption) _
, 0, "EXCEL;", vbNullString) _
, 0, "combobox", vbNullString))
End Sub

Then I tried this and worked:

Sub DefinedNames()
Application.Dialogs(xlDialogDefineName).Show , "=" & ActiveSheet.Name &
"!" & ActiveCell.Address
End Sub

What am I missing?

Thanks
Hamilton R. Romano

 

[Chip Pearson]

Did you assign a shortcut key to the SetFocusNameBox macro?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


I got this from Chip Pearson´s site, but don´t works for me (Win
XP,
Excel 2003 portuguese).

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()
Dim Res As Long
Res = SetFocus( _
FindWindowEx( _
FindWindowEx( _
FindWindow("XLMAIN", Application.Caption) _
, 0, "EXCEL;", vbNullString) _
, 0, "combobox", vbNullString))
End Sub

Then I tried this and worked:

Sub DefinedNames()
Application.Dialogs(xlDialogDefineName).Show , "=" &
ActiveSheet.Name &
"!" & ActiveCell.Address
End Sub

What am I missing?

Thanks
Hamilton R. Romano

 

[Bob Phillips]

You could also try Ctrl-F3

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

I got this from Chip Pearson´s site, but don´t works for me (Win XP,
Excel 2003 portuguese).

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()
Dim Res As Long
Res = SetFocus( _
FindWindowEx( _
FindWindowEx( _
FindWindow("XLMAIN", Application.Caption) _
, 0, "EXCEL;", vbNullString) _
, 0, "combobox", vbNullString))
End Sub

Then I tried this and worked:

Sub DefinedNames()
Application.Dialogs(xlDialogDefineName).Show , "=" & ActiveSheet.Name &
"!" & ActiveCell.Address
End Sub

What am I missing?

Thanks
Hamilton R. Romano

 

[keepITcool]

Chip's code assumes the "Formula Bar" is visible
THEN put's the focus on the name combobox
(located at the left side of the formula bar.)

The DefineName dialog is something else...

 

[hamiltonrr]

Thank you guys.

Hamilton R. Romano

keepITcool escreveu: