JKP Application Development Services.

Microsoft Office Application Development

Module Code & Conclusion

Up • UserForm Setup • How It Works • Module Code & Conclusion

• •

Search

Zoeken

Go to	Ga Naar
Home Up

Donate	Donaties
Did you find something helpful on my site? Consider a donation! Heeft u iets gevonden waar u wat aan had? Overweeg dan een donatie!

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

Module code

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

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.

Add a comment too!!!

Subscribe in a reader