Can we read the ColumnWidths of a ListBox ?

[Jaafar Tribak]

Hi,

I need to read the width of each column in a multi-column ListBox ... It seems that the ColumnWidths Property is Write Only.
Am I missing something ? Any thoughts ?

Thank you.

 

[ISY]

Hi

By Michael Erickson

ListBox Column Width - Auto Resize according to text length that we get from the cell ?

Hello, i am using Search textbox (searching data all cells in all workbook) and get datas to in Listbox.Some cells have long definitions and doesn't fit in column in listBox. Can we do auto fit in a simple way ? the file u can find easily on the web : Find All VBA Form - All Worksheets.xls

www.excelforum.com

 

[Norie]

I seem to be able to read it.

Private Sub CommandButton1_Click()
    MsgBox Me.ListBox1.ColumnWidths
End Sub

 

[YasserKhalil]

Based on Nories' reply, you can iterate to read each column individually using Split

Private Sub CommandButton1_Click()
    Dim e
    For Each e In Split(Me.ListBox1.ColumnWidths, ";")
        Debug.Print e
    Next e
End Sub

 

[Jaafar Tribak]

Thanks guys.

MsgBox Me.ListBox1.ColumnWidths Returns an empty string for Sheet ListBoxes as well as for UserForms Listboxes. (I am using Excel 2016 if that makes a difference and I had the Listbox populated using RowSource)

 

[YasserKhalil]

Have you specified the column widths property like that for example: 10 pt;20 pt;30 pt ??
I have tested on Office 365 and it worked .. I have assigned RowSource for the listbox on userform

 

[Jaafar Tribak]

Have you specified the column widths property like that for example: 10 pt;20 pt;30 pt ??
I have tested on Office 365 and it worked .. I have assigned RowSource for the listbox on userform

Good point YasserKhalil !

I didn't set the column widths prior to reading them ... The code indeed works if the column widths are explicitly set before hand either via code or via the Properties Window in the VBE. So thank you for that.

I wonder if there is a Property or another way to read the individual column widths if they are not set beforehand.

I noticed that, if the column widths are not explicitly set, Excel gives each individual column the default fixed width of (≅ 82.5 pt) but I am not sure that this value will be always the same regardless of the user specifics.

 

[RoryA]

According to the documentation:

To calculate column widths when ColumnWidths is blank or -1, the width of the control is divided equally among all columns of the list. If the sum of the specified column widths exceeds the width of the control, the list is left-aligned within the control and one or more of the rightmost columns are not displayed. Users can scroll the list using the horizontal scroll bar to display the rightmost columns.

The minimum calculated column width is 72 points (1 inch). To produce columns narrower than this, you must specify the width explicitly.

 

[Jaafar Tribak]

According to the documentation:

Thanks. That answers my question.