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

File crashes Excel!!

Save my workbook!
The best tool for salvaging problematic workbooks.

Trainings

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

Third party tools

Speed up your file

FastExcel
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 (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

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

 


Comment by: Jan Karel Pieterse (7/13/2013 7:11:34 PM)

Hi

You can. Replace this:

    ActiveSheet.UsedRange.Select
    ShowTable Selection.Value, "Test", True


with:

    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 (7/13/2013 10:33:33 PM)

It comes up blank its not showing data

 


Comment by: Paul Forster (7/13/2013 10:51:57 PM)

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 (10/9/2015 10:38:06 PM)

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 (10/12/2015 11:34:53 AM)

Hi Bruce,

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

 


Comment by: John (11/30/2016 6:07:46 PM)

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 (11/30/2016 6:11:37 PM)

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 (11/30/2016 9:15:50 PM)

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: www.eileenslounge.com.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

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