Showing An Array On A Form; Autosizing ColumnWidths Of A ListBox

Module code

To use the form, you can use this generic function:

'-------------------------------------------------------------------------
' Module    : modShowTable
' Company   : JKP Application Development Services (c)
' Author    : Jan Karel Pieterse (www.jkp-ads.com)
' Created   : 2-4-2008
' Purpose   : Shows a table on ufTable
'-------------------------------------------------------------------------
Option Explicit

Public Function ShowTable(vTable As Variant, sTableTitle As String, bAutoColWidths As Boolean) As Variant
'-------------------------------------------------------------------------
' Procedure : ShowTable
' Company   : JKP Application Development Services (c)
' Author    : Jan Karel Pieterse (www.jkp-ads.com)
' Created   : 2-4-2008
' Purpose   : Shows vTable on the userform ufShowTable, with a maximum width and height.
'-------------------------------------------------------------------------
    Dim frmShowTable As ufShowTable
    On Error GoTo LocErr
    Set frmShowTable = New ufShowTable
    With frmShowTable
        .Table = vTable
        .Title = sTableTitle
        .Caption = GSAPPNAME
        .AutoColWidths = bAutoColWidths
        .Initialise
        .Show
    End With
TidyUp:
    On Error GoTo 0
    Exit Function
LocErr:
    Select Case ReportError(Err.Description, Err.Number, "ShowTable", "Module modShowTable")
    Case vbRetry
        Resume
    Case vbIgnore
        Resume Next
    Case vbAbort
        Resume TidyUp
    End Select
End Function

You use the function like this:

Sub demo()
'-------------------------------------------------------------------------
' Procedure : demo
' Company   : JKP Application Development Services (c)
' Author    : Jan Karel Pieterse (www.jkp-ads.com)
' Created   : 14-5-2008
' Purpose   : Shows the usedrange on the form
'-------------------------------------------------------------------------
    ActiveSheet.UsedRange.Select
    ShowTable Selection.Value, "Test", True
End Sub

Conclusion

As you've seen it takes a little bit of trickery to get this to work. We've used a (hidden) label control with AutoSize set to True and WordWrap to False. Then we fetch that label's width to determine the width the text will occupy in the listbox.

Download the sample file

Comments

All comments about this page:

Comment by: Andy Pope (5/15/2008 12:41:28 AM)

This is exactly how I do my column sizing for listboxes.

But there is no need to repeat the resize if you set the Width property to a larger
value. Try setting it to 9999 and comment out the duplication.

Comment by: Jan Karel Pieterse (5/15/2008 4:10:27 AM)

Hi Andy,

I adjusted the code based on a suggestion by Stephen Bullen and now the repeat is no
longer needed.

Comment by: Redge (12/19/2010 5:34:35 PM)

Thank you for such a detailed presentation. I have been working on a small utility and found myself wondering how to get the automatic resizing to work. This absolutely answered the question!

Thank you.

Comment by: Jan Karel Pieterse (12/20/2010 5:24:52 AM)

Hi Redge,

You're welcome!

Always nice if I hear about people being able to use something from my website.

Comment by: Ceelly (7/12/2011 10:25:43 PM)

Hi,

Found your article when I was haiving a problem with column widths needing to be adjustable.. thanks your code really worked. Takes a bit to get your head around as I am not familar with object orinated code but it was well worth it.

thanks

Ceelly

Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.