Avatar of Murray Brown
Murray BrownFlag for United Kingdom of Great Britain and Northern Ireland

asked on 

Access VBA widen ListBox columns to fit data

I am using the following code to fill a listbox in Access but the columns are not wide enough. How do I widen the columns to automatically fit the data

Private Sub Report_Open()

    Dim Query As DAO.QueryDef

    Debug.Print oReportSQL
    If oReportSQL <> "" Then
        Set Query = CurrentDb.CreateQueryDef("", oReportSQL)
        Me!ListSql.ColumnCount = Query.Fields.Count
        Me!ListSql.RowSource = oReportSQL
    End If

End Sub

Open in new window

User generated image
Microsoft AccessVBA

Avatar of undefined
Last Comment
Murray Brown
Avatar of Jonathan Kelly
Jonathan Kelly
Flag of Ireland image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Adding to Jonathan's solution you could also do a "selective" iteration e.g. get random n records to find the widest text entries and use this code : https://social.msdn.microsoft.com/Forums/en-US/2727e4a4-57a3-4e4d-a20a-314464579ad3/how-to-calculate-the-width-of-a-access-form-textbox-pending-on-font-and-length-of-characters-string?forum=isvvba
to adjust the columns nicely
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

or you could use a datasheet subform instead of the listbox.  Added advantages of:
1. justification within a column
2. Sorting and filtering in the datasheet
3.  Setting column widths on the fly
4.  Hiding columns the user doesn't need
5.  Moving columns to a user specified sequence.
You can even allow users to save their preferences when you do it this way.  Check out my article on Customizing Datasheets to Users needs
Avatar of Murray Brown
Murray Brown
Flag of United Kingdom of Great Britain and Northern Ireland image


Thanks very much
Microsoft Access
Microsoft Access

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo