Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Name Box
How to change the size of the 'Name Box'.. ie make it wider
-- LJP |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Name Box
In Excel 2003 and earlier, you cannot change the size of the Name box
on the formula bar. However, with some code, you can change the width of the drop down list, the list of names that appears when you click the arrow. See http://www.cpearson.com/Excel/NameBox.aspx for code to widen the drop down. Cordially, Chip Pearson Microsoft MVP Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Tue, 2 Dec 2008 11:51:01 -0800, Lois wrote: How to change the size of the 'Name Box'.. ie make it wider |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Name Box
To the best of my knowledge, you can't change the width of the Name Box on the Formula Bar itself, but you can widen its dropdown box which should help you some. Place all the code below into a Module (Insert/Module on the VBA editor's menu bar) and then execute the WidenNameBoxDropDown macro from any worksheet. Note that the macro makes the dropdown box 200 pixels wide. You can control this width by changing the 200 in this line of code...
SendMessage hWndNameCombo, CB_SETDROPPEDWIDTH, 200, 0 It is the last line of code in the WidenNameBoxDropDown macro. Okay, here is the code... 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 CB_SETDROPPEDWIDTH As Long = &H160& ' Make the Name dropdown list 200 pixels wide Public Sub WidenNameBoxDropDown() 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) ' Set the dropdown list to be 200 pixels wide SendMessage hWndNameCombo, CB_SETDROPPEDWIDTH, 200, 0 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 -- Rick (MVP - Excel) "Lois" wrote in message ... How to change the size of the 'Name Box'.. ie make it wider -- LJP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|