Manipulating windows and controls in Excel
Nile_Hef
the Excel boards are more focused on VBA: this board may be a better forum
for VB code and Windows API programming.
Many years ago, Chip Pearson published code to widen the 'named ranges'
combo box in the Excel Formula Bar. His solution widens the dropped list, but
the visible 'textbox' of this control stays the same size... Which is,
annoyingly, too narrow for the names and range addresses used in a complex
workbook. Chip's original code is here:
http://www.cpearson.com/excel/NameBox.htm
I've revisited the code, and can now widen the whole control using
SetWindowPos.
Unfortunately the widened textbox starts impinging on the drop-down button
and, if widened further, it eventually covers the formula dialog controls.
So a complete solution needs an enumeration of all the controls on the
formula bar, so that they can be shuffled along to the right to accommodate
the widened textbox. This is a little bit beyond my API coding skills -
anyone prepared to offer a few pointers?
Here's the code:
' **** **** **** **** **** **** **** **** ****
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String _
) 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 Declare Function GetWindowRect Lib "user32" _
(ByVal hwnd As Long, _
ByRef lpRect As RECT _
) As Long
Private Declare Function SetWindowPos Lib "user32" _
(ByVal hwnd As Long, _
ByVal hwndRel As Long, _
ByVal lngLeft As Long, _
ByVal lngTop As Long, _
ByVal lngWidth As Long, _
ByVal lngHeight As Long, _
ByVal lngFlags As Long _
) As Long
Private Type RECT
Left As Long
Top As Long
Right As Long
Bottom As Long
End Type
Public Sub WidenNameBox()
' Nigel Heffernan
' Widen the Named Range drop-down box on the main application window.
' Adapted from code published by Chip Pearson http://www.cpearson.com
' Call WidenNameBox from the Workbook_Open event procedure
' REQUIRED API DECLARATIONS:
' FindWindow, FindWindowEx, SendMessage
' GetWindowRect, SetWindowPos
Dim hwndApp As Long ' Excel Application Window
Dim hwndMain As Long ' Client area handle
Dim hwndCtrl As Long ' Combo box control handle
Dim rectCtrl As RECT
Dim lngLeft As Long
Dim lngTop As Long
Dim lngWidth As Long
Dim lngHeight As Long
Const CB_SETDROPPEDWIDTH As Long = &H160
Const NEW_WIDTH As Long = 350
Const SWP_NOMOVE As Long = 2
hwndApp = FindWindow("XLMAIN", Application.Caption)
hwndMain = FindWindowEx(hwndApp, 0&, "EXCEL;", vbNullString)
hwndCtrl = FindWindowEx(hwndMain, 0&, "combobox", vbNullString)
' Widen the dropped-down list
SendMessage hwndCtrl, CB_SETDROPPEDWIDTH, NEW_WIDTH, 0&
GetWindowRect hwndCtrl, rectCtrl
With rectCtrl
lngLeft = .Left
lngTop = .Top
lngWidth = .Right - .Left
lngHeight = .Bottom - .Top
End With
lngWidth = lngWidth + 5
' Widen the static textbox portion of the drop-down list
SetWindowPos hwndCtrl, 0, lngLeft, lngTop, lngWidth, lngHeight, SWP_NOMOVE
' Why does the relative position parameter for the Z-Order
' work with a zero or NULL value, but hide the control when
' passed the parent window handle hwndMain?
End Sub
' **** **** **** **** **** **** **** **** ****
RB Smissaert
Private Declare Function FindWindow _
Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) 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
Sub SetNameBoxWidth(lWidth As Long)
Dim lXLHwnd As Long
Dim lXLDesktopHwnd As Long
Dim lXLNameBoxHwnd As Long
Const CB_SETDROPPEDWIDTH = &H160
lXLHwnd = FindWindow("XLMAIN", Application.Caption)
lXLDesktopHwnd = FindWindowEx(lXLHwnd, _
0, _
"EXCEL;", _
vbNullString)
lXLNameBoxHwnd = FindWindowEx(lXLDesktopHwnd, _
0, _
"combobox", _
vbNullString)
SendMessage lXLNameBoxHwnd, CB_SETDROPPEDWIDTH, lWidth, 0
End Sub
RBS
"Nile_Hef" <Nil...@discussions.microsoft.com> wrote in message
news:F449EE81-2538-4CA8...@microsoft.com...
Nile_Hef
attempting to do, but not all of it: I want to widen the text frame as well
as the dropped-down list.
Actually, your code is identical to mine if widening the text frame is
excluded.
The RECT type and its dimensions, and the API calls to GetWindowRect and
SetWindowPos are the added extras, and the problems I am still trying to fix
will probably be resolved by some alternative to the SetWindowPos call.
RB Smissaert
RBS
"Nile_Hef" <Nil...@discussions.microsoft.com> wrote in message
news:90E7BDC4-4D94-4173...@microsoft.com...
Nile_Hef
> Why do you want to widen the non-dropdown window?
The facetious answer is: "Because it's too narrow!" - Apologies, but it's
difficult to give an answer that won't sound slightly patronising.
The specific problem is a workbook I've been given, with eight hundred named
ranges like:
TenantSummary.DistributionByRentAreaRatioTable
TenantSummary.DistributionByUsageArea
TenantSummary.DistributionByUsageERV
TenantSummary.DistributionByUsageOriginalRent
TenantSummary.DistributionByUsageUsage
TenantSummary.TopTenantsByAreaArea
TenantSummary.TopTenantsByAreaAreaCumulativePercentage
TenantSummary.TopTenantsByAreaAreaPercentage
TenantSummary.TopTenantsByAreaERV
TenantSummary.TopTenantsByAreaOriginalRent
TenantSummary.TopTenantsByAreaTenantName
TenantSummary.TopTenantsbyRent
The names box only displays 13 chars. I need to know what the selected cell
is called, and slightly faster than three clicks. I can't even rename the
ranges: they are mapped to database tables.
That's the specific point: in general, the window is too narrow to display a
reasonably informative set of names in anything bigger than a mom-and-pop
business solution.
RB Smissaert
A wider non-dropped box will show you one range in full, but that won't help
you much with 799 other names.
The code I posted will widen the dropped box and as you have to drop it in
any case won't that be enough?
RBS
"Nile_Hef" <Nil...@discussions.microsoft.com> wrote in message
news:E644CE38-D6F8-4BA8...@microsoft.com...
RB Smissaert
> three clicks.
OK, overlooked that one, so now I got you!
Why not simply add some code that will display the name of a range if over x
characters in the Excel statusbar?
You could put this code in an add-in or Personal.xls.
Much simpler and clearer at the same time.
RBS
"RB Smissaert" <bartsm...@blueyonder.co.uk> wrote in message
news:eXh1MTFo...@TK2MSFTNGP04.phx.gbl...
RB Smissaert
In a normal workbook go to the VBE and right-click ThisWorkbook and
do View code.
Paste this code:
Option Explicit
Private WithEvents xlApp As Excel.Application
Private Sub Workbook_Open()
Set xlApp = Excel.Application
End Sub
Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)
On Error GoTo ERROROUT
If Len(Target.Name.Name) > 15 Then
Application.StatusBar = Target.Name.Name
End If
Exit Sub
ERROROUT:
Application.StatusBar = False
End Sub
Save the workbook as an add-in (.xla), call it something like ShowLongNames
or whatever.
Easiest to save to the default .xla folder.
In Excel: Tools, Add-ins, Tick the add-in you created.
This will put your long names in the statusbar, without causing any other
trouble.
RBS
"RB Smissaert" <bartsm...@blueyonder.co.uk> wrote in message
news:%2371RZYF...@TK2MSFTNGP02.phx.gbl...
RB Smissaert
Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)
On Error GoTo ERROROUT
If Len(Target.Name.Name) > 10 Then
Application.StatusBar = String(8, Chr(32)) & Target.Name.Name
Else
Application.StatusBar = False
End If
Exit Sub
ERROROUT:
Application.StatusBar = False
End Sub
RBS
"RB Smissaert" <bartsm...@blueyonder.co.uk> wrote in message
news:ed1zODG...@TK2MSFTNGP04.phx.gbl...
Rick Rothstein
You might find it interesting to know that VBA has a Space function that
does what your String function is doing...
Space(8) is equivalent to String(8, Chr(32))
Rick
RB Smissaert
RBS
"Rick Rothstein" <rickNOS...@NOSPAMcomcast.net> wrote in message
news:OEgzzxHo...@TK2MSFTNGP05.phx.gbl...