listbox columnwidth how it works? RRS feed

  • Question

  • can someone explain the secrets of the list-boxes columnwidth. I have some knowledge of the columnwidth but want to learn more on how it works.

    Jim Neely

    Thursday, June 21, 2012 8:16 PM


  • OMG!!!!!! You don't know how LONG I have been working on this. Thank You Thank You

    Jim Neely

    • Marked as answer by STARFALLS Sunday, June 24, 2012 9:43 PM
    Sunday, June 24, 2012 9:15 PM

All replies

  • Hello Jim,

    I will cover a few thing that I am aware of and if that does not cover what you want then maybe you will have specific questions about other things not covered. Also others might be able to expand on this post with further information.

    1. When you set the number of columns in a list box it appears to have a default value for the width of each column. I have never been able to find that default ColumnWidth value.

    2. The default measurement for ColumnWidths is in Points. Can be changed to inches or centimetres. Enter "ColumnWidths Property" (without the quotes) in Help for more info on this.

    3. To set the ColumnWidths in properties using the default points measurement just enter as 50;40;60;30;50;50;50 etc for the number of columns. Excel will change this to look like the following: 49.95 pt;40 pt;60 pt;30 pt;49.95 pt;49.95 pt;49.95 pt. Note that the actual widths might change with decimal places and the pt is added. (You can actually enter the data using a comma separator and Excel will change it to a semi colon.)

    4. I cannot read any values for the column widths if they have been left as default and the ColumnWidths property appears blank. However, the following code will read the individual column widths if the ColumnWidths property is populated by first placing them in an array.

    Private Sub CommandButton1_Click()
        'Return the ListBox column widths
        Dim i As Long
        Dim varTemp As Variant
        Dim arrColWidths As Variant
        varTemp = Me.ListBox1.ColumnWidths
        'varTemp = Replace(varTemp, "pt", "") 'If only values without pt to be returned.
        If varTemp <> "" Then   'If blank then cannot read ColumnWidths.
            arrColWidths = Split(varTemp, ";")
            For i = LBound(arrColWidths) To UBound(arrColWidths)
                MsgBox arrColWidths(i)
            Next i
            MsgBox "ColumnWidths have not been set"
        End If
    End Sub

    5. The following code can be used to build a string to set the column widths. In the example it simply sets them all to 50 pt but you could build a string with different values for each column. I do not know any way of setting an individual ColumnWidth; need to set them all together with a string. You could write code to identify the individual widths and simply adjust the specific column to change but settin the property must take place as the entire string covering all columns.

    Private Sub CommandButton2_Click()
        Dim strListBoxColumns As String
        Dim i As Long
        'Build a string for the column widths
        For i = 0 To Me.ListBox1.ColumnCount - 1
            strListBoxColumns = strListBoxColumns & "50;"
        Next i
        Me.ListBox1.ColumnWidths = strListBoxColumns

    End Sub

    Hope it helps and feel free to get back with any further questions.

    Regards, OssieMac

    Friday, June 22, 2012 4:31 AM
  • Great information. I have a few more questions for you of the practical nature.

    1) If i hide cells does the columnwidth work on the next visible column. why?

    2) I have 17 columns but the last few do not show in the listbox

    3) How are headers useable?

    4) Is there a limit to the number of columns?

    Jim Neely

    Friday, June 22, 2012 3:50 PM
  • Hello Jim,

    Q1. I can't emulate anything where the ListBox column widths are related to column widths. Not saying it doesn't because we just might be on a different wave length.

    Q2. If the columns on the right are not visible then you should have a scroll bar on the bottom so you can access them.

    Q3. Note: With an ActiveX ListBox on a worksheet you have a ListFillRange and with a ListBox on a Userform it is called RowSource. Column headers can only be used if the ListFillRange/RowSource is set to a worksheet range. You set the ListFillRange/RowSource to start on the row below the headers and then the system identifies the first row above as the column headers. Column headers cannot be selected in the ListBox.

    Q4. I don't know if there is a limit on the number of ListBox columns. However, I should think that if you have a great number of columns then the ListBox is not the answer. Probably should be using a worksheet with AutoFilter and make your selections that way.

    Regards, OssieMac

    Saturday, June 23, 2012 3:44 AM
  • Thanks for your help.

    Q2 and Q3

    Here is my dilemma, my last two rows do not show up at all. Here is the code i am using.

     Worksheets("Gages").AutoFilter.Range.SpecialCells(xlCellTypeVisible).Copy _
    'Following assigns the data only (excluding the column headers) to rngSource
    With Worksheets("ListBoxData")
    'Set rngSource = .Range(.Cells(2, "A"), .Cells(.Rows.count, "W").End(xlUp))
    Set rngSource = .Range("A2:W1450")
    End With
    'Fill the listbox
    Set lbtarget = Me.ListBox1
    With lbtarget
        'Determine number of columns
        .ColumnCount = 27
        'Set column widths
        '.ColumnWidths = "100;65;125;0;0;35;30;30;0;0;0;0;0;0;0;0;40;40;40;40;0;0;0;0;0;20;20"
        'Use the above line for Development and the below line for Operations
        .ColumnWidths = "100;65;125;30;30;30;40;40;40;0;0;0;0;0;0;100;20;0;0"
        'Insert the range of data supplied
        .ColumnHeads = True
        '.List = rngSource.Cells.Value
        .RowSource = rngSource.Address(External:=True)
    End With

    Jim Neely

    Sunday, June 24, 2012 9:04 AM
  • The range A2:W1450 or Range(.Cells(2, "A"), .Cells(.Rows.count, "W").End(xlUp)) has 23 columns (the alphabet A...Z has 26 letters, you're using A...W, so you're omitting X, Y and Z; that leaves 23).

    Your list box has 27 columns, so the last 4 columns will remain empty.

    Regards, Hans Vogelaar

    Sunday, June 24, 2012 9:47 AM
  • OMG!!!!!! You don't know how LONG I have been working on this. Thank You Thank You

    Jim Neely

    • Marked as answer by STARFALLS Sunday, June 24, 2012 9:43 PM
    Sunday, June 24, 2012 9:15 PM