Manipulating windows and controls in Excel

125 views
Skip to first unread message

Nile_Hef

unread,
Jun 22, 2006, 7:11:02 AM6/22/06
to
*Crossposted from excel.programming * Apologies for the cross-posting, but
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

unread,
Jul 2, 2006, 3:55:34 PM7/2/06
to
I use this simpler code and have no problems with it:

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

unread,
Jul 4, 2006, 10:47:02 AM7/4/06
to
Thank you, but your code is simpler because it does part of what I am
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

unread,
Jul 4, 2006, 1:30:17 PM7/4/06
to
Why do you want to widen the non-dropdown window?

RBS

"Nile_Hef" <Nil...@discussions.microsoft.com> wrote in message

news:90E7BDC4-4D94-4173...@microsoft.com...

Nile_Hef

unread,
Jul 5, 2006, 12:54:01 PM7/5/06
to

> 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

unread,
Jul 5, 2006, 1:02:26 PM7/5/06
to
Sorry, still don't quite get it.
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

unread,
Jul 5, 2006, 1:11:42 PM7/5/06
to
> I need to know what the selected cell is called, and slightly faster than
> 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

unread,
Jul 5, 2006, 2:28:23 PM7/5/06
to
In case you didn't know how to do that:

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

unread,
Jul 5, 2006, 5:38:57 PM7/5/06
to
Just some little modifications:

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

unread,
Jul 5, 2006, 5:46:18 PM7/5/06
to
> Application.StatusBar = String(8, Chr(32)) & Target.Name.Name

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

unread,
Jul 5, 2006, 5:52:55 PM7/5/06
to
Yes, forgot about that one and thanks for reminding.

RBS

"Rick Rothstein" <rickNOS...@NOSPAMcomcast.net> wrote in message
news:OEgzzxHo...@TK2MSFTNGP05.phx.gbl...

Reply all
Reply to author
Forward
0 new messages