Re: hide name box

Anyone know how to hide (not display) the name box *but still* display
the formula bar?

How about if we leave it displayed, but clear the edit field and then disable it? Add a Module and copy/paste all the code after my signature into its code window. To use it, simply execute this code...

To disable Name Box: EnableNameComboBox False

To re-enable Name Box: EnableNameComboBox True

Note: I have cobbled this together from many different sources. I have left in (or expanded on) the comments that were included with the original code in case you want to "tinker" with the code some.

Rick

' Enables or disables a window. If a window is disabled, it cannot
' receive the focus and will ignore any attempted input. Some types
' of windows, such as buttons and other controls, will appear grayed
' when disabled, although any window can be enabled or disabled. The
' function returns 0 if the window had previously been enabled, or a
' non-zero value if the window had been disabled.
Private Declare Function EnableWindow Lib "user32.dll" _
(ByVal hwnd As Long, _
ByVal fEnable As Long) As Long

' Get the handle of the desktop window
Private Declare Function GetDesktopWindow Lib "user32" () As Long

' Get the process ID of this instance of Excel
Private Declare Function GetCurrentProcessId Lib "kernel32" () As Long

' Get the ID of the process that a window belongs to
Private Declare Function GetWindowThreadProcessId Lib "user32" _
(ByVal hwnd As Long, _
ByRef lpdwProcessId As Long) As Long

Private 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

Private Declare Function SendMessage Lib "user32" _
Alias "SendMessageA" _
(ByVal hwnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
lParam As Any) As Long

Private Const WM_SETTEXT As Long = 12&

'Make the Name dropdown list 200 pixels wide
Public Sub EnableNameComboBox(State As Boolean)
Dim hWndFormulaBar As Long
Dim hWndNameCombo As Long
'Get the handle for the formula bar window
hWndFormulaBar = FindWindowEx(ApphWnd(), 0, "EXCEL;", vbNullString)
'Get the handle for the Name combobox
hWndNameCombo = FindWindowEx(hWndFormulaBar, 0, "combobox", vbNullString)
' Clear the NAME box's edit field
SendMessage hWndNameCombo, WM_SETTEXT, ByVal 0, ByVal ""
' Disable the NAME box
EnableWindow hWndNameCombo, State
End Sub

' Get the main Excel window's hWnd
Private Function ApphWnd() As Long
'Excel 2002 and above have a property for the hWnd
If Val(Application.Version) >= 10 Then
ApphWnd = Application.hwnd
Else
ApphWnd = FindOurWindow("XLMAIN", Application.Caption)
End If
End Function

' Finds a top-level window of the given class and caption that
' belongs to this instance of Excel, by matching the process IDs
Private Function FindOurWindow(Optional sClass As String = vbNullString, _
Optional sCaption As String = vbNullString)
Dim hWndDesktop As Long
Dim hwnd As Long
Dim hProcThis As Long
Dim hProcWindow As Long
'Get the ID of this instance of Excel, to match to
hProcThis = GetCurrentProcessId
' All top-level windows are children of the desktop,
' so get that handle first
hWndDesktop = GetDesktopWindow
Do
' Find the next child window of the desktop that matches the given
' window class and/or caption. The first time in, hWnd will be zero,
' so we'll get the first matching window. Each call will pass the
' handle of the window we found the last time, thereby getting the
' next one (if any)
hwnd = FindWindowEx(hWndDesktop, hwnd, sClass, sCaption)
' Get the ID of the process that owns the window
GetWindowThreadProcessId hwnd, hProcWindow
' Loop until the window's process matches this process,
' or we didn't find a window
Loop Until hProcWindow = hProcThis Or hwnd = 0
' Return the handle we found
FindOurWindow = hwnd
End Function

.

Relevant Pages

  • Re: Creating new appointment & setting its properties
    ... The window and process subclassing I've seen with WordMail is horrendous, it's miles of code just to get a true hWnd for a WordMail window usually. ... Private Declare Function GetForegroundWindow Lib "User32.dll" As Long ... Private Declare Function FindWindowEx Lib "User32.dll" Alias "FindWindowExA" (ByVal hwndParent As Long, ByVal hwndChildAfter As Long, ByVal lpszClass As String, ByVal lpszWindow As String) As Long ...
    (microsoft.public.outlook.program_vba)
  • Re: Are VBA userforms always child of Windows desktop?
    ... Private Declare Function GetDesktopWindow Lib "user32" As Long ... Private Declare Function GetParent Lib "user32" (ByVal hwnd As Long) As Long ... The system restores the window to its original state. ... Dim hWndDesktop As Long ...
    (microsoft.public.excel.programming)
  • Re: Are VBA userforms always child of Windows desktop?
    ... I only need this Hwnd of the parent, ... GetAncestor API (with GA_PARENT) to get Parent. ... I suppose which window you need will depend on the overall objective. ... Private Declare Function GetDesktopWindow Lib "user32" As Long ...
    (microsoft.public.excel.programming)
  • Re: Hide the Name Box
    ... ' Enables or disables a window. ... (ByVal hwnd As Long, _ ... Private Declare Function GetDesktopWindow Lib "user32" As Long ...
    (microsoft.public.excel.programming)
  • Re: Hide the Name Box
    ... ' Enables or disables a window. ... (ByVal hwnd As Long, _ ... Private Declare Function GetDesktopWindow Lib "user32" As Long ...
    (microsoft.public.excel.programming)