|
|
 Author |
 Named Ranges (currently 139 views) |
| chee |
| Posted on: September 10th, 2004, 8:49am |
  |
|
Active Member
Posts: 14
|
|
Does anyone suffer from long Named Ranges where the Name Box in Excel will not display the whole string? i.e the box only display upto so many characters and if you have named ranges that are similar except for maybe the last few characters which means you are unable to distinguish which named range is which.
If so I have come across a VBA macro which will solve this issue. If you would like a copy drop me a email and I'll forward a copy.
Chee |
|
 Logged |
|
|
|
|
| Stephen Aldridge |
| Posted on: September 14th, 2004, 10:58am |
|
|
 Administrator
Creator of financialmodelling.net
Posts: 71
|
|
Chee
This sounds useful. I would be interested to see it. It has also given me an idea for a new section of this discussion group, about utilities. I will set up a utilities discussion for anyone to describe or link to useful utilities. If you want to put the file on this site, it is possible to link it to a post - I'll leave this up to you if you prefer to do that or to respond to requests by email.
Thanks for your contribution
Best regards
Stephen |
|
| Logged |
|
|
|
|
| chee |
| Posted on: September 30th, 2004, 3:52pm |
|
|
Active Member
Posts: 14
|
|
Here is the code:
Option Explicit
'// Set up the API's
Private Declare Function GetWindowDC Lib "user32" ( _
ByVal hwnd As Long) As Long
Private Declare Function GetTextExtentPoint32 Lib "gdi32" _
Alias "GetTextExtentPoint32A" ( _
ByVal hdc As Long, _
ByVal lpsz As String, _
ByVal cbString As Long, _
lpSize As POINTAPI) As Long
Private Declare Function SendMessage Lib "user32" _
Alias "SendMessageA" ( _
ByVal hwnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
ByVal lParam 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 FindWindowA Lib "user32" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Private Declare Function GetSystemMetrics Lib "user32.dll" ( _
ByVal nIndex As Long) As Long
Public Type POINTAPI
x As Long
y As Long
End Type
'// Define Scrn Constants
Private Const SM_CXSCREEN = 0
'// Define ComboBox Constants
Private Const CB_SETDROPPEDWIDTH = &H160
Private Const CB_GETDROPPEDWIDTH = &H15F
'// Define Message
Private Const msg As String = "Could NOT set the NameBox width!"
'// Define some Class Constants
Private Const strDropBtnClass As String = "ComboBox" 'Name Box Class
Private Const strXLClass As String = "XLMAIN" 'Main Xl Window Class
Private Const strXLChildClass As String = "EXCEL;" 'FormulaBar Class
Public Sub ReSizeNameBoxWidth()
'//////////////////////////////////////////////////////
'// Resize the Excel Name Box [ComboBox dropdown width]
'// Developement Enviroment:
'// : Excel2000 / Win 98
'// : Excel2000 / WinXp(Home)
'// Created by Ivan F Moala 15th September 2002
'// Tests:
'// Tested Excel XP and XL2000 on WinMe
'// By Juan Pablo G.
'// MrExcel.com Consulting
'// Tom Utis MrExcel MVP
'// Tested XL 2002 on Windows 2000 SP-3
'// Windows Me - Swedish
'// Windows 2000 - Swedish SP-3
'// Windows XP - Swedish
'// Excel -versions:
'// XL 97 SR2b - Swedish
'// XL 2000 SP-1 - Swedish
'// XL 2002 SP-2 - English / Swedish.
'// Test result: No error reported
'// Wishlist:
'// That the namebox lenght adjust automatically to
'// the longest used named.
'// By XL Dennis
'// Amended 21st Dec 2002 automatically set width to
'// longest string (NOT dynamically!)
'//////////////////////////////////////////////////////
Dim hwndXl As Long '// Child window that contains combobox
Dim xlMain As Long '// Xl Window handle
Dim hwndcbo As Long '// Handle of Name Box dropdown
Dim lSetWidth As Long '// setting for new width
Dim lScrnWidth As Long '// Screen Width
Dim Ret As Long '// Return Function success variable
'//
Dim lSetH As Long
'// Get Xls handle ie. Main Wnd
xlMain = FindWindowA(strXLClass, vbNullString)
'// Get Child Wnd
hwndXl = FindWindowEx(xlMain, 0, strXLChildClass, vbNullString)
'// NOW Get Handle of the Name Box
hwndcbo = FindWindowEx(hwndXl, 0, strDropBtnClass, vbNullString)
lScrnWidth = GetSystemMetrics(SM_CXSCREEN)
'// Need to look @ 0.89 scale factor!?
lSetWidth = (GetcboxTxtLen(hwndcbo) * 0.89) + 10
'// In case > screen width!
If lSetWidth > lScrnWidth Then
lSetWidth = lScrnWidth
End If
Ret = SendMessage(hwndcbo, CB_SETDROPPEDWIDTH, lSetWidth, 0)
If Ret = 0 Then MsgBox msg, vbInformation
End Sub
Function GetcboxTxtLen(cboxhnd As Long) As Long
Dim strNames As String
Dim aNames()
Dim ind As Integer
Dim DC As Long
Dim Tmp As Long
Dim TextLargest As Long
Dim TextSize As POINTAPI
For ind = 1 To ActiveWorkbook.Names.Count
ReDim Preserve aNames(ind)
aNames(ind) = Names(ind).Name
Next
If ActiveWorkbook.Names.Count = 0 Then
Exit Function
Else
For ind = 1 To UBound(aNames)
'// Get DeviceContext of Combobox
DC = GetWindowDC(cboxhnd)
'// Get measurements of Text in pixels
GetTextExtentPoint32 DC, aNames(ind), Len(aNames(ind)), TextSize
Tmp = TextSize.x
If Tmp > TextLargest Then TextLargest = Tmp
Next ind
GetcboxTxtLen = TextLargest
End If
End Function
rgds,
Chee |
|
| Logged |
|
|
  |
Reply: 2 - 2 |
|
|
|
| |
| Forum Rules |
You may not post new threads
You may not post replies
You may not post polls
You may not post attachments
|
HTML is off
Blah Code is on
Smilies are on
|
|
|
|
Financialmodelling.net Discussions › Useful stuff on the web › Utilities › Named Ranges
Locked Board