asked on

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

ASKER CERTIFIED SOLUTION

Jonathan Kelly

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

John Tsioumpris

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

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

ASKER

Thanks very much