Anyone know how to hide (not display) the name box *but still* display
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