Columns Size in List for VBA Access

[Joaquin256]

Hi,

II am working on a query form in access, where I have a textbox in which I type the value to search for, which will then load them in the listed object (as if it were a datagrid or a list).
The problem is that it brings me the data correctly, but I would like the columns to automatically adjust to the size of the data or at least change the size of each one manually through a VBA code.
Any solution?
From already thank you very much

[#2]

@Joaquin256 

Could you explain in a bit more detail what this "listed object" is?

Are you talking about a form in datasheet view, perhaps? Or something else.

[#3]

@Joaquin256 

if it is a Datasheet you are talking, you can Resize the Column by setting the ColumnWidth = -2 (best fit).

you can add code to the "subform" Load event:

Private Sub Form_Load()
Dim ctl As Control
For Each ctl In Me.Detail.Controls
    If ctl.ControlType = 109 Then
        ctl.ColumnWidth = -2
    End If
Next ctl
End Sub

Public Sub ResizeColumns()
Call Form_Load
End Sub

you also need code to the AfterUpdate event of your textbox:

Private Sub TextSearch_AfterUpdate()
Me.theSubformName.Requery
Me.theSubformname.Form.ResizeColumns
End Sub

[#4]

@George_Hepworth First of all thank you for your reply. this is an object in a type form or named "list box"

[#5]

Can that routine be applied to a list box?

[#6]

@Joaquin256 

Okay,  you want to manage both column widths in Datasheet view forms, and column widths in list boxes.

The code Arnel offered would apply to a form, but that form would have to be in datasheet view.

It can't impact the columns in a list box, though.

You can also manage column widths in list boxes, albeit with a lot more coding. You have a couple of choices here. If you must use a list box to display results, then the process is going to be fairly involved. Or, you can implement the form as a datasheet in a subform in the form with the searching textbox.

Here's a link, by the way, to a complete solution for column widths in datasheet view forms along with discussions about it.

This link is to a solution that allows you to adjust widths of columns in a list box.

Stephen Lebans has not been active for years, but his code is still among the best available in some situations like this. If you have any problems using it, please post back.

[#7 · Accepted solution]

@Joaquin256 

see this demo using Stephen Lebans code (class).