Showing An Array On A Form; Autosizing ColumnWidths Of A ListBox
Content
Introduction
For a project I needed a quick way to display the content of an array to the user. I didn't want to use a worksheet, but opted for a userform. The data I wanted to show was contained in an array. The array contained something like:
So I figured I'd put a listbox on a userform and make sure the column widths of the listbox resize with the data I want shown. That proved far from easy...
UserForm Setup
I devised a userform that looks like this:
The form contains these controls (and I list the most important properties too):
To be able to use the form I have added a couple of properties and methods to use:
Which all sums up to this VBA code behind the UserForm:
' Module : ufShowTable
' Company : JKP Application Development Services (c)
' Author : Jan Karel Pieterse (jkp-ads.com)
' Created : 14-5-2008
' Purpose : Code to handle showing of table on this userform
'-------------------------------------------------------------
Option Explicit
Private mvTable As Variant
Private mbAutoColWidths As Boolean
Private mdFormWidth As Double
Private mdFormHeight As Double
'Code for form resizing courtesy:
'Stephen Bullen, www.oaltd.co.uk
'Rob Bovey, www.appspro.com
'Declare an object for the CFormResizer class to handle resizing for this form
Dim mclsResizer As CFormResizer
'----------------------EVENT CODE ----------------------
Private Sub cmbClose_Click()
Me.Hide
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Comments: When the form is resized, the UserForm_Resize event
' is raised, which we just pass on to the Resizer class
'
' Date Developer Action
' ------------------------------------------------------
' 07 Oct 04 Stephen Bullen Initial version
'
Private Sub UserForm_Resize()
If mclsResizer Is Nothing Then Exit Sub
mclsResizer.FormResize
End Sub
'----------------------METHODS----------------------
Public Sub Initialise()
'---------------------------------------------------
' Procedure : Initialise
' Company : JKP Application Development Services (c)
' Author : Jan Karel Pieterse (jkp-ads.com)
' Created : 14-5-2008
' Purpose : Initialises the form and makes sure the listbox resizes according to the data
'-------------------------------------------------------------------------
Dim lRowCt As Long
Dim lColCt As Long
Dim lLengths() As Long
On Error GoTo LocErr
On Error GoTo LocErr
ReDim lLengths(UBound(mvTable, 2))
With lbxTable
.Clear
.ColumnCount = UBound(mvTable, 2) + 1
For lRowCt = LBound(mvTable, 1) To UBound(mvTable, 1)
For lColCt = LBound(mvTable, 2) To UBound(mvTable, 2)
'Store the largest string length of each column of the array
lLengths(lColCt) = Application.Max(4, lLengths(lColCt), Len(mvTable(lRowCt, lColCt)))
If lColCt = LBound(mvTable, 2) Then
'first item has to be added through additem
.AddItem mvTable(lRowCt, lColCt)
Else
.List(.ListCount - 1, lColCt - 1) = CStr(mvTable(lRowCt, lColCt))
End If
Next
Next
End With
If AutoColWidths Then
'Now autosize the ColumnWidths
SetWidths lLengths()
End If
'Create the instance of the form resizer class
Set mclsResizer = New CFormResizer
'Tell it where to store the form dimensions
mclsResizer.RegistryKey = GSREGKEY
'Tell it which form it's handling
Set mclsResizer.Form = Me
'Temporarily disable adjusting lbxtable, it has been sized already
lbxTable.Tag = ""
'Adjust dimensions of form using new dimensions of the listbox
'The form_resize event handles the positioning of the other controls on the form
Me.Width = lbxTable.Left + lbxTable.Width + 12
Me.Height = lbxTable.Top + lbxTable.Height + 30 + cmbClose.Height
'Enable size of listbox again
lbxTable.Tag = "WH"
TidyUp:
On Error GoTo 0
Exit Sub
LocErr:
Select Case ReportError(Err.Description, Err.Number, "Initialise", "Form ufShowTable")
Case vbRetry
Resume
Case vbIgnore
Resume Next
Case vbAbort
Resume TidyUp
End Select
End Sub
Private Function SetWidths(lLengths() As Long)
'--------------------------------------------------
' Procedure : SetWidths
' Company : JKP Application Development Services (c)
' Author : Jan Karel Pieterse (jkp-ads.com)
' Created : 14-5-2008
' Purpose : Sets the column widths of the listbox according to an array of max text lengths
'--------------------------------------------------
Dim lCt As Long
Dim sWidths As String
Dim dTotWidth As Double
On Error GoTo LocErr
For lCt = 1 To UBound(lLengths)
With lblHidden
'Using repeating letter m to determine width because that is a relatively wide letter.
'To ensure text always fits, use capital M instead
.Caption = String(lLengths(lCt), "m")
End With
dTotWidth = dTotWidth + lblHidden.Width
If Len(sWidths) = 0 Then
sWidths = CStr(Int(lblHidden.Width) + 1)
Else
sWidths = sWidths & ";" & CStr(Int(lblHidden.Width) + 1)
End If
Next
'Now set the widths of the columns
lbxTable.ColumnWidths = sWidths
'Adjust the dimensions of the listbox itself. You may want to adjust the constants
'I hard coded here.
'Listbox will always be at least 200 wide
lbxTable.Width = Application.Min(Application.Max(200, dTotWidth + 12), lbxTable.Width)
'Listbox will always be at least 48 high.
lbxTable.Height = Application.Min(Application.Max((lbxTable.ListCount + 1) * 12, 48), lbxTable.Height)
TidyUp:
On Error GoTo 0
Exit Function
LocErr:
Select Case ReportError(Err.Description, Err.Number, "SetWidths", "Form ufShowTable")
Case vbRetry
Resume
Case vbIgnore
Resume Next
Case vbAbort
Resume TidyUp
End Select
End Function
'----------------------PROPERTIES----------------------
Public Property Get Table() As Variant
Table = mvTable
End Property
Public Property Let Table(ByVal vTable As Variant)
mvTable = vTable
End Property
Public Property Let Title(ByVal sTitle As String)
lblTableTitle.Caption = sTitle
End Property
Public Property Get AutoColWidths() As Boolean
AutoColWidths = mbAutoColWidths
End Property
Public Property Let AutoColWidths(ByVal bAutoColWidths As Boolean)
mbAutoColWidths = bAutoColWidths
End Property
Public Property Get FormWidth() As Double
FormWidth = Me.Width
End Property
Public Property Let FormWidth(ByVal dFormWidth As Double)
Me.Width = dFormWidth
End Property
Public Property Get FormHeight() As Double
FormHeight = Me.Height
End Property
Public Property Let FormHeight(ByVal dFormHeight As Double)
Me.Height = dFormHeight
End Property
The attentive reader will have spotted that I hard-coded some constants into the code. Of course if you develop this thing for yourself, you'd have used constants or some other way to control (for example) the maximum column widths.
How The Resize Works
Various people have devised a trick to calculate the column widths. Some
use constants, which which the number of characters to be displayed is multiplied.
This trick doesn't work reliably since screen resolution and Font may affect
this.
The best method I know of is by using a (hidden) label with its AutoSize
property set to True. This label must have the same Font characteristics
as the ListBox. After changing the Caption of the Label, one can read its
width to fetch the size needed for the text.
The trick to make the resizing work lies in the Function SetWidths behind
the userform. I pass an array of character counts to the function (containing
the maximum # of characters for each column to be shown in the listbox).
Then for each column I change the caption of the label to an equal amount
of characters as the value in the array. I use the same character, since
the Font for the label is proportional. Thus, the letter used will determine
what width the label gets.
Then I read the label's width and string that value together to form the
ColumnWidths string (widths delimited by a ;).
The tricky part are the properties of the label. Set the WordWrap to False and the AutSize to True to make this work.
The end results looks like this:
Great, isn't it?
Module code
To use the form, you can use this generic function:
' Module : modShowTable
' Company : JKP Application Development Services (c)
' Author : Jan Karel Pieterse (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 (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:
'-------------------------------------------------------------------------
' Procedure : demo
' Company : JKP Application Development Services (c)
' Author : Jan Karel Pieterse (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 (15-5-2008 00:41:28) deeplink to this comment
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 (15-5-2008 04:10:27) deeplink to this comment
Hi Andy,
I adjusted the code based on a suggestion by Stephen Bullen and now the repeat is no
longer needed.
Comment by: SHAKEEL (18-5-2008 10:11:42) deeplink to this comment
It's nice I am happy with it, but I am looking for something would help me learn
more about formulas & Charts.
Thanks for the Information you have provided me..........
Comment by: Jan Karel Pieterse (19-5-2008 00:01:43) deeplink to this comment
Hi Shakeel,
Try this site for lots of general Excel help:
<a target="_blank" href="http://www.cpearson.com/">Pearson Software Consulting
Services</a>
Comment by: Jan Karel Pieterse (19-5-2008 00:02:23) deeplink to this comment
and this site for charting:
<a target="_blank" href="http://www.peltiertech.com/">Peltiertech</a>
Comment by: Redge (19-12-2010 17:34:35) deeplink to this comment
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 (20-12-2010 05:24:52) deeplink to this comment
Hi Redge,
You're welcome!
Always nice if I hear about people being able to use something from my website.
Comment by: Ceelly (12-7-2011 22:25:43) deeplink to this comment
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
Comment by: Paul Forster (13-7-2013 18:32:43) deeplink to this comment
first of all its amazing file. however i want the button on one page "sheet1" but i need the form to get its information from another sheet "Progress Schedule" within the same workbook. and i dont know how as im what people call a noob. lol
anyway thanks again
paul
Comment by: Jan Karel Pieterse (13-7-2013 19:11:34) deeplink to this comment
Hi
You can. Replace this:
ShowTable Selection.Value, "Test", True
with:
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) deeplink to this comment
It comes up blank its not showing data
Comment by: Paul Forster (13-7-2013 22:51:57) deeplink to this comment
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) deeplink to this comment
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) deeplink to this comment
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) deeplink to this comment
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) deeplink to this comment
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) deeplink to this comment
Thanks, Jan
I did what you said and it worked
Comment by: KEVIN BRISEBOIS (8-5-2020 22:15:00) deeplink to this comment
Hi Jan,
i ran into the same problem of +10 columns.
our answer is:
If you push the data to the listbox using a variant array you should be able to have many more columns than 10.
.. how can i do this?
Thanks!
Comment by: Jan Karel Pieterse (11-5-2020 10:22:00) deeplink to this comment
Hi Kevin,
In the userform code on page https://jkp-ads.com/Articles/AutoSizeListBox01.asp, in this sub: Initialise replace this bit:
.Clear
.ColumnCount = UBound(mvTable, 2) + 1
For lRowCt = LBound(mvTable, 1) To UBound(mvTable, 1)
For lColCt = LBound(mvTable, 2) To UBound(mvTable, 2)
'Store the largest string length of each column of the array
lLengths(lColCt) = Application.Max(4, lLengths(lColCt), Len(mvTable(lRowCt, lColCt)))
If lColCt = LBound(mvTable, 2) Then
'first item has to be added through additem
.AddItem mvTable(lRowCt, lColCt)
Else
.List(.ListCount - 1, lColCt - 1) = CStr(mvTable(lRowCt, lColCt))
End If
Next
Next
End With
with
With lbxTable
.Clear
.ColumnCount = UBound(mvTable, 2) + 1
.List = mvTable
End With
For lRowCt = LBound(mvTable, 1) To UBound(mvTable, 1)
For lColCt = LBound(mvTable, 2) To UBound(mvTable, 2)
'Store the largest string length of each column of the array
lLengths(lColCt) = Application.Max(4, lLengths(lColCt), Len(mvTable(lRowCt, lColCt)))
Next
Next
End Sub
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.