Dynamically assign Column Widths in a ListBox

[pkrish]

Hi All!


I am trying to dynamically assign column width for each column in a listbox. I am calculating the maximum length of a string in a column and assigning maximum length + 5 as the column width. But when the list box pops up after the code is executed, all the column values still remain truncated which clearly shows that column widths are not assigned at all.


I would really appreciate it if you could please tell how I need to modify the code.


Thanks,

PK


____________________________________________________________________________________

[pre]

Sub Mstr_Project()

Dim Column_Count As Integer
Dim Row_Count As Integer

Dim i As Integer 'row counter
Dim j As Integer ' column counter

Dim strLength As Integer
Dim MaximumStringLength As Integer

Table.Label1.Caption = "[dbo].[mstr_Project]"

Column_Count = Worksheets("Description").Range("Mstr_Project").Columns.Count

Row_Count = Worksheets("Description").Range("Mstr_Project").Rows.Count

Table.ListBox1.ColumnCount = Column_Count
Table.ListBox1.List = Worksheets("Description").Range("Mstr_Project").value

For j = 1 To Column_Count  'count of columns

For i = 1 To Row_Count   'count of rows within jth column

strLength = Len(Worksheets("Description").Range("Mstr_Project").Cells(i,   j).value)

If strLength > MaximumStringLength Then
MaximumStringLength = strLength
End If

Next i

Table.ListBox1.ColumnWidths = MaximumStringLength + 5
Debug.Print Table.ListBox1.ColumnWidths

Next j

Table.Show

End Sub

[/pre]

 

[SirJB7]

Hi, pkrsih!


This is copied from my Excel version help file, it's in Spanish. I think you can either translate it to English or finding your native local version equivalent (search for "ColumnWidths").


-----

[pre]

Control ListBox. Ejemplo de las propiedades ColumnCount y ColumnWidths

El siguiente ejemplo utiliza la propiedad ColumnWidths para cambiar los anchos de las columnas de un control ListBox de múltiples columnas. El ejemplo también utiliza tres controles TextBox para especificar los anchos de columna individuales y utiliza el evento Exit para especificar las unidades de medida de cada control TextBox.

Para utilizar este ejemplo, copie este código de ejemplo en la parte Declaraciones de un formulario. Asegúrese de que el formulario contiene: 

Un control ListBox llamado ListBox1.

Tres controles TextBox llamados TextBox1 hasta TextBox3.

Un control CommandButton llamado CommandButton1.
Intente escribir el valor 0 para ocultar una columna.

Dim MyArray(2, 3) As String

Private Sub CommandButton1_Click()
'ColumnWidths requires a value for each column
'separated by semicolons
ListBox1.ColumnWidths = TextBox1.Text & ";" _
& TextBox2.Text & ";" & TextBox3.Text
End Sub
Private Sub TextBox1_Exit(ByVal Cancel As _
MSForms.ReturnBoolean)
'ColumnWidths accepts points (no units), inches
'or centimeters; make inches the default
If Not (InStr(TextBox1.Text, "in") > 0 Or _
InStr(TextBox1.Text, "cm") > 0) Then
TextBox1.Text = TextBox1.Text & " in"
End If
End Sub
Private Sub TextBox2_Exit(ByVal Cancel As _
MSForms.ReturnBoolean)
'ColumnWidths accepts points (no units), inches
'or centimeters; make inches the default
If Not (InStr(TextBox2.Text, "in") > 0 Or _
InStr(TextBox2.Text, "cm") > 0) Then
TextBox2.Text = TextBox2.Text & " in"
End If
End Sub
Private Sub TextBox3_Exit(ByVal Cancel as MSForms.ReturnBoolean)
'ColumnWidths accepts points (no units), inches or
'centimeters; make inches the default
If Not (InStr(TextBox3.Text, "in") > 0 Or _
InStr(TextBox3.Text, "cm") > 0) Then
TextBox3.Text = TextBox3.Text & " in"
End If
End Sub
Private Sub UserForm_Initialize()
Dim i, j, Rows As Single

ListBox1.ColumnCount = 3
Rows = 2

For j = 0 To ListBox1.ColumnCount - 1
For i = 0 To Rows - 1
MyArray(i, j) = "Row " & i & ", Column " & j
Next i
Next j
'Load MyArray into ListBox1
ListBox1.List() = MyArray
'1-inch columns initially
TextBox1.Text = "1 in"
TextBox2.Text = "1 in"
TextBox3.Text = "1 in"
End Sub

[/pre]
-----


As summary, ControlWidths is a string separated by semicolons (I think that in English might be commas) with the width in points for each column. An unquoted value of "50,100,200" will set 1st column width to 50 points , 2nd to 100 and 3rd to 200. A value of "MaximumStringLength + 5" as in your code will only change width for 1st column.


Hope it helps.


Regards!