Most Valuable Professional

View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.


Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Listbox AutoSize > Module Code and Conclusion
Deze pagina in het Nederlands

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 (
' 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 (
' 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
    End With
    On Error GoTo 0
    Exit Function
    Select Case ReportError(Err.Description, Err.Number, "ShowTable", "Module modShowTable")
    Case vbRetry
    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 (
' Created   : 14-5-2008
' Purpose   : Shows the usedrange on the form
    ShowTable Selection.Value, "Test", True
End Sub


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



Showing last 8 comments of 14 in total (Show All Comments):


Comment by: Jan Karel Pieterse (13-7-2013 19:11:34)


You can. Replace this:

    ShowTable Selection.Value, "Test", True


    ShowTable Worksheets("Sheet1").UsedRange.Value, "Test", True

Then put a button from the forms set on the other sheet and assign the macro called "Demo" to it.


Comment by: Paul Forster (13-7-2013 22:33:33)

It comes up blank its not showing data


Comment by: Paul Forster (13-7-2013 22:51:57)

ok got it working had to use a button that allows macro's. however the autosize is not working and ideas


Comment by: Bruce Volkert (9-10-2015 22:38:06)

I have a workbook in Office 365 that generates a couple of tables (listobject) in a couple of sheets in that workbook. I have several other workbooks that need to use the information, some of which may need to add or change the information.

I'm struggling to find a simple approach (preferably one that does not require using a separate database). I'd prefer to stick to Excel because the application is relatively simple and my users have very limited computing skills.

I started to think I would use ADO to access the table using a connection and then update it from time to time with new information. However, my current understanding is that ADO is not able to delete records when the connection is to a listobject in a workbook. Since my users will need to create and change the records in the table, it seems that ADO may not be the best choice.

Is my best option to read all the information in, change it within Excel and then create a new version of the independent file? If not, what approach do you suggest?


Comment by: Jan Karel Pieterse (12-10-2015 11:34:53)

Hi Bruce,

As you have experienced, deleting records from an Excel table is not possible using ADO.


Comment by: John (30-11-2016 18:07:46)

Thanks for the detailing of the demo, very nicely done.

I ran into a problem and I don't know if this can be fixed... The problem is that if I load more than 10 columns, it does not load the data into the form. I don't know if this is the limitation of the Listbox...

Thanks again.


Comment by: Jan Karel Pieterse (30-11-2016 18:11:37)

Hi John,

If you push the data to the listbox using a variant array you should be able to have many more columns than 10.


Comment by: John (30-11-2016 21:15:50)

Thanks, Jan
I did what you said and it worked


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:

Please enter your name (required):

Your e-mail address (optional, will only be used to inform you when your comment is published or to respond to your question directly):

Your request or comment (max 2000 characters):

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].

I give permission to process this data and display my name and my comment on this website accoring to our Privacy Policy.