ComboBox listwidth to match text in list

[J.S.Winstrom]

I am having some trouble in finding information on how to set the
listwidth/columnwidths in a combobox to match the text longest entry. Any
help would be greatly apreciated!

~J.S.Winstrom

 

[Jim Thomlinson]

If you want the size of the list box to change with each time chosen that
that is not to bad. Using a combo box from the Control Toolbox change the
AutoFit Property to True.

If you want to set the size of the box to be as long as it need to be for
the longest entry then that is more difficult than it sounds. The problem
arises because by default you are probably using a proporational font like
Tahoma or Arial where the with of each character varies. Based on that it is
very difficult to know which is the "longest" entry and what the required
width is. If you Switch to courier or some other non-proportional font then
you can determine the length of each character based on the font size and
proportion your width accordingly. My suggestion for this is to just make the
Box as big as you reasonably can...

 

[Tom Ogilvy]

autofit the column without wraptext and set the column width to the Width
property of the column. Make sure the fonts and font size are the same.

 

[Jim Thomlinson]

You have completely lost me... Combo Boxes dont' have a wraptext property and
lots of what you discuss just does not seem to fit... I'm probably missing
something?

 

[Atomic Storm]

=?Utf-8?B?SmltIFRob21saW5zb24=?=
on Thu 02 Aug
2007 12:58:07p:

You have completely lost me... Combo Boxes dont' have a wraptext
property and lots of what you discuss just does not seem to fit... I'm
probably missing something?

Seems I haven't explained this correctly. I how to set the combobox width
through the properties using autosize = true - however, I am looking to set
the .listwidth and .columnwidths of the dropdown list via the largest text
(listindex) entry. Something like:

Dim getCount as String
getCount = ComboBox1.ListCount
For iNum = 0 to getCount
With ComboBox1
.ListWidth = .List(iNum).Width
End With
if iNum = getCount - 1 then Exit For
Next i

Which doesn't quite work... I tried it.

Hopefully that's a little more clear of what I'm after

Thanks for the input!

~J.S.Winstrom

 

[Gary Keramidas]

this is untested and i'm not sure if it's even what you want.

once the combobox is populated, this would run and create an array of the length
of each entry:

For z = 0 To ComboBox1.ListCount - 1
arr(z) = Len(ComboBox1.List(z))
Next

then:
Application.Max(arr)

would return the element with the length of the longest entry
maybe you can use that to set the width, not sure what you want.

 

[J.S.Winstrom]

this is untested and i'm not sure if it's even what you want.

once the combobox is populated, this would run and create an array of
the length of each entry:

For z = 0 To ComboBox1.ListCount - 1
arr(z) = Len(ComboBox1.List(z))
Next

then:
Application.Max(arr)

would return the element with the length of the longest entry
maybe you can use that to set the width, not sure what you want.

I don't know about the Application.Max(arr), but would I be able to do a
ComboBox1.ListWidth = arr ?

 

[Jim Thomlinson]

Gary - The problem with what you have shown is that len returns the number of
characters, not the sum of the widths of all of the characters. Each
character is a different size.

Atomic - To your question about list width / column width. Unless you choose
a non-proportional font like courier you are going to run into problems. The
width of i and the width of z are very different. If you coose courier then
you can traverse your list of entries an determine the max number of
characters an multiply that by the size of 1 character to determine your
width... My preference is still just to hard code a width that will
accomodate darn near anything.

 

[J.S.Winstrom]

=?Utf-8?B?SmltIFRob21saW5zb24=?=
on Thu 02 Aug
2007 02:22:01p:

Gary - The problem with what you have shown is that len returns the
number of characters, not the sum of the widths of all of the
characters. Each character is a different size.

Atomic - To your question about list width / column width. Unless you
choose a non-proportional font like courier you are going to run into
problems. The width of i and the width of z are very different. If you
coose courier then you can traverse your list of entries an determine
the max number of characters an multiply that by the size of 1
character to determine your width... My preference is still just to
hard code a width that will accomodate darn near anything.

Jim: Thanks again for helping out. The only problem with hard-coding the
length is there is too much of a range in widths. The data loaded into any
given combo in my userform is loaded from an external access database. So
the text length could range from "A" to
"Supercalifragilisticexbeealidocious". So, if I can, I need to figure out a
method or formula of determining the width of the largest entry at any
given time.

Gary: Thanks for the input. Unfortunately, James is right. It returned the
largest number of characters and set the listwidth to 10 points when, in
actuality, it needed to be about 52 points.

 

[Jim Thomlinson]

Since you now know the max number of characters set your combo box font to
courier and multiply the characters by approximately 5 (depends on your font
size). This will give you the max width. You can stay with Tahoma or Arial
but you will need to determine your max character size and use that. This
will make the box a bit too large but it is the closest you will get without
a whole pile of work.

 

[J.S.Winstrom]

=?Utf-8?B?SmltIFRob21saW5zb24=?=
on Thu 02 Aug
2007 03:00:01p:

Since you now know the max number of characters set your combo box
font to courier and multiply the characters by approximately 5
(depends on your font size). This will give you the max width. You can
stay with Tahoma or Arial but you will need to determine your max
character size and use that. This will make the box a bit too large
but it is the closest you will get without a whole pile of work.

Awesome! So, I have a multi-columned combo...
I think I can figure it out from here.

Just for fun, when I get the code figured out, I'll post it.

Thank you so much for the help!

~J.S.Winstrom

 

[Gary Keramidas]

thanks, jim, kind of knew that. but i figured it could used with fixed font or
multiplied, like you suggested, by the number of points in the widest character.
it was just an example or idea to stimulate an answer.

 

[Jim Thomlinson]

I must be lazy. I woulda just made the size really freakin big... but to each
his own...

 

[Tom Ogilvy]

Here is what I mean Jim.

Private Sub UserForm_Initialize()
With Worksheets("Sheet1").Columns(6)
c = .ColumnWidth
.WrapText = False
.AutoFit
Me.ListBox1.ColumnWidths = .Width + 10
Me.ListBox1.Width = .Width + 20
Me.ListBox1.Font.Name = _
.Parent.Range("F10").Font.Name
Me.ListBox1.Font.Size = _
.Parent.Range("F10").Font.Size
.ColumnWidth = c
DoEvents
End With
End Sub

It worked perfectly for me. The OP would have to address restoring wraptext
to the Column IN THE WORKSHEET if that were a problem.

But I see you have led him on a merry dance, so hopefully he is happy.

 

[J.S.Winstrom]

in on Fri 03 Aug
2007 09:24:02a:

Here is what I mean Jim.

Private Sub UserForm_Initialize()
With Worksheets("Sheet1").Columns(6)
c = .ColumnWidth
.WrapText = False
.AutoFit
Me.ListBox1.ColumnWidths = .Width + 10
Me.ListBox1.Width = .Width + 20
Me.ListBox1.Font.Name = _
.Parent.Range("F10").Font.Name
Me.ListBox1.Font.Size = _
.Parent.Range("F10").Font.Size
.ColumnWidth = c
DoEvents
End With
End Sub

It worked perfectly for me. The OP would have to address restoring
wraptext to the Column IN THE WORKSHEET if that were a problem.

But I see you have led him on a merry dance, so hopefully he is happy.

For the most part, I'm headed in the right direction. I just need to
figure out how to load the array with the widths of the text entries and
the compare them to each other to find which one is the largest. Once I
can hack that data apart, I'll have what I need. But for the record, I
wasn't talking about a cell on a worksheet like you referenced in your
example. I was refering to a combobox list on a userform. So maybe that
line could be altered to say something to reference Combobox.Font.Size?

Anyways, thanks again guys for the help.

~J.S.Winstrom

 

[J.S.Winstrom]

on Thu 02 Aug
2007 11:32:12a:

I am having some trouble in finding information on how to set the
listwidth/columnwidths in a combobox to match the text longest entry.
Any help would be greatly apreciated!

~J.S.Winstrom

I found a method that worked. As suggested by Jim, I used a factor of 5
and here's the code that I came up with to dynamicaly set the .ListWidth
and .ColumnWidths:

(getCount is already declared as a Public variable because I use it
everywhere and this rountine is nested within the Userform_Activate()
routine after the commands that fetch data from am Access database and
populate the list)

_______________________________________________________

Dim col1Width, col2Width, c1Width, c2Width, iNum
getCount = .ListCount - 1
c1Width = Len(.List(getCount, 0)) * 5
c2Width = Len(.List(getCount, 1)) * 5
For iNum = 0 To getCount
col1Width = Len(.List(iNum, 0)) * 5
col2Width = Len(.List(iNum, 1)) * 5
If col1Width > c1Width Then
If col2Width > c2Width Then
.ColumnWidths = col1Width & ";" & col2Width
Else
.ColumnWidths = col1Width & ";" & c2Width
End If
ElseIf col1Width < c1Width Then
If col2Width > c2Width Then
.ColumnWidths = c1Width & ";" & col2Width
Else
.ColumnWidths = c1Width & ";" & c2Width
End If
End If

If Val(col1Width + col2Width) > Val(c1Width + c2Width) Then
.ListWidth = Val(col1Width + col2Width)
ElseIf Val(c1Width + col1Width) > Val(c2Width + col2Width) Then
.ListWidth = Val(c1Width + col1Width)
ElseIf Val(c1Width + col2Width) > Val(col1Width + c2Width) Then
.ListWidth = Val(c1Width + col2Width)
ElseIf Val(col1Width + col2Width) < Val(c1Width + c2Width) Then
.ListWidth = Val(col1Width + col2Width)
ElseIf Val(c1Width + col1Width) < Val(c2Width + col2Width) Then
.ListWidth = Val(c1Width + col1Width)
ElseIf Val(c1Width + col2Width) < Val(col1Width + c2Width) Then
.ListWidth = Val(c1Width + col2Width)
End If
If iNum = getCount Then Exit For
Next iNum

 

[Tom Ogilvy]

That is what I am talking about as well, however, the text originated on a
worksheet. If not, then it is trivial to put it on a scratch sheet and use
this method but that may may not seem elegant and whatever method you are
pursuing may be more preferred.

Assuming a single column Combobox o rlistbox (listbox here)
v = Me.ListBox1.List
For i = LBound(v) To UBound(v)
If Len(v(i, LBound(v, 2))) > maxlen Then
maxlen = Len(v(i, LBound(v, 2)))
End If
Next
MsgBox maxlen

will get your maxlen in terms of characters if that is what you were asking.

 

[Tom Ogilvy]

Just a thought, but why change your settings on each turn of the loop?
Seems like it would be faster to get the values and set them once after the
loop is completed.

 

[J.S.Winstrom]

in on Fri 03 Aug
2007 02:00:01p:

Just a thought, but why change your settings on each turn of the loop?
Seems like it would be faster to get the values and set them once
after the loop is completed.

Well, I tried setting the values into an array, but I couldn't get the
array to work right because I don't know too much about them. So this was
my answer to not using an array.

 

[J.S.Winstrom]

on Fri 03 Aug
2007 02:52:30p:

=?Utf-8?B?VG9tIE9naWx2eQ==?= <[email protected]>
wrote in on
Fri 03 Aug 2007 02:00:01p:


Well, I tried setting the values into an array, but I couldn't get the
array to work right because I don't know too much about them. So this
was my answer to not using an array.

I also didn't see your other post.
I'm going to test the code from your other post and see if that might be a
better solution.

One question: doesn't maxlen have to be set before all that code runs to
avoid a null value error?