Subscribe in a readerShowing 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
' 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
'-------------------------------------------------------------------------
' 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.
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.
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.