dynamic column widths

Hello,

I am trying to set my column widths based on my longest string in my list box. What I need to do is to iterate through the list box items and find the longest string. The code below is what I have so far. I think I'm close, but I just get a length of 2 for every item, which I know is incorrect. Could someone please point out where I went wrong.



Dim idx As Integer
Dim textwidth As Integer
Dim ColWidth As Integer

ColWidth = 0

For idx = 0 To ListBox2.ListCount - 1
textwidth = Len(idx)
If textwidth > ColWidth Then
ColWidth = textwidth
End If
Next idx

ListBox2.ColumnWidths = ColWidth



Thanks,
Eugene

 

I use the following it works on a ListBox called lstBatch and it also requires a TextBox called txtListScroll. The TextBox is set with a Visible property = False and Autosize = True.
The ListBox values are sent to the TextBox so the width of the ListBox can be set to the AutoSized width of the TextBox.
Regards - Nathan

Code:

Private Sub SetScroll()
Dim intListCount As Integer
Dim intCount As Integer
intListCount = lstBatch.ListCount
If intListCount <> 0 Then
txtListScroll = lstBatch.list(0)
lstBatch.ColumnWidths = txtListScroll.Width
End If
intCount = 0
While intCount <> intListCount
txtListScroll = lstBatch.list(intCount)
If lstBatch.ColumnWidths < txtListScroll.Width & " pt" Then
lstBatch.ColumnWidths = txtListScroll.Width
End If
intCount = intCount + 1
Wend
End Sub

 

Thanks for the reply Nathan. In all due respect, your solution seems a
little convoluted for what I'm trying to accomplish. Do you really need a
text box to accomplish this task? Can you see the problem with the code I've
supplied? Thanks for your help!

-Eugene

 

idx is a 2-byte integer counter (not to be confused with the no good 2-bit
counter). To get the length of the contents of the listbox item, use:
Len(ListBox2.list(idx))

But this will just tell you the number of characters, not the actual width
of the string as printed on the screen. You need to use Nathan's solution,
or something similar, to do that.

James


Hello,

I am trying to set my column widths based on my longest string in my list
box. What I need to do is to iterate through the list box items and find the
longest string. The code below is what I have so far. I think I'm close, but
I just get a length of 2 for every item, which I know is incorrect. Could
someone please point out where I went wrong.



Dim idx As Integer
Dim textwidth As Integer
Dim ColWidth As Integer

ColWidth = 0

For idx = 0 To ListBox2.ListCount - 1
textwidth = Len(idx)
If textwidth > ColWidth Then
ColWidth = textwidth
End If
Next idx

ListBox2.ColumnWidths = ColWidth



Thanks,
Eugene

 

I use the following for an app that lists layer names stripped
of their source prefixes:

ListWidth = Max(MaxNWidth * Wfudge + 8#, MinListW)

Max is function returning the Max.
MaxNWidth is the maximum char. len for the items in the list.
Wfudge is a tweek factor:
Wfudge = ufLayerBox.lboxLList.Font.Size * 0.75
8# was another tweek factor
MinListW is the width I do not want the listbox width to be smaller than.

 

The problem is you can not simply get the width of an item in a listbox so you need to either apply a fudge scaling factor to the number of characters like AKS is doing or use my solution which is still very simple.

 

That's news to me.

It can be gotten by using the appropriate Windows API
functions

- GetWindowDC()

- TextOut() or GetTextExtentPoint32()

Sorry, no samples in VBA, but it isn't very difficult to
do, given knowledge of how to use the above APIs.

 

Okay! Thanks for the help guys, I think I understand now. Nathan, I hope I
didn't come across as being rude. I really appreciate your help.

Best Regards
Eugene

 

I like that autosize phantom textbox idea Nathan.

 

You could try with the API function "GetTextExtentPoint32".

 

Would you have an example of how to do that?

 

How do you get the hwnd or hdc of a VBA control?

 

I don't have any examples, but you could just try something with these functions.

Private Declare Function SelectObject Lib "gdi32" (ByVal hdc As Long, ByVal hObject As Long) As Long

Private Declare Function DeleteObject Lib "gdi32" (ByVal hObject As Long) As Long

Private Declare Function CreateFontIndirect Lib "gdi32" Alias "CreateFontIndirectA" (lpLogFont As LOGFONT) As Long

Private Declare Function GetActiveWindow Lib "user32.dll" () As Long

Private Declare Function GetWindowDC Lib "user32" (ByVal hwnd As Long) As Long

Private Declare Function ReleaseDC Lib "user32" (ByVal hwnd As Long, ByVal hdc As Long) As Long
Private Declare Function GetTextExtentPoint32 Lib "gdi32.dll" _
Alias "GetTextExtentPoint32A" ( _
ByVal hdc As Long, _
ByVal lpsz As String, _
ByVal cbString As Long, _
lpSize As Size) As Long

Private Type Size
cx As Long
cy As Long
End Type

Private Type LOGFONT
lfHeight As Long
lfWidth As Long
lfEscapement As Long
lfOrientation As Long
lfWeight As Long
lfItalic As Byte
lfUnderline As Byte
lfStrikeOut As Byte
lfCharSet As Byte
lfOutPrecision As Byte
lfClipPrecision As Byte
lfQuality As Byte
lfPitchAndFamily As Byte
lfFaceName As String * 32
End Type

First you need to call the function "GetActiveWindow" on the load/activate event of your userform to get its handle. Then use the handle to get the device content with the function "GetWindowDC" and then use the DC in "GetTextExtentPoint32" to get the length of the string in pixels. I am not sure if you would need to create a font object (same as in the listbox) to get the length of the string as drawn on the listbox. I included the functions and structure you need for that. Hope it helps.

 

I seem to recall that you can use EnumChildWindows(), or
set the focus to the control of interest, and then call the
GetFocus() WinAPI function.

Sorry, I have no examples (the development tools I use
don't require me to jump through these kind of hoops).

 

Thanks for correcting me Tony. I wish you had have been there when I was originally trying to work it out.
Regards - Nathan

 

Seems like a lot less hoops to just use a hidden autosizing textbox.

 

Here's an example for ComboBoxes that you might be able to repurpose.
http://vbnet.mvps.org/code/comboapi/combowidthadv.htm

Hope that helps,
Danny Polkinhorn
WATG
Honolulu