asked on 5/14/2021

Access VBA widen ListBox columns to fit data

Hi
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
        Query.Close
    End If

End Sub

Open in new window

Microsoft Access VBA

Last Comment

Murray Brown

5/15/2021

ASKER CERTIFIED SOLUTION

Jonathan Kelly

5/14/2021

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

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

John Tsioumpris

5/14/2021

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

Dale Fye

5/14/2021

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

Murray Brown

5/15/2021

ASKER

Thanks very much