ListView - automatic ColumnWidth 1

[Speridal]

Hi @all,

is it possible to automatically resize the columns of a listbox?
I would like to fill a listview object with a select-statement. When it is filled I would like to bring the column widths to an optimal value.

Thx in advance.

Greets,
Speridal

 

[SBendBuckeye]

Are you talking about some kind of an auto resize feature based on the returned data? If so, you can force a query to use a function if it is declared in a standard module. If you pass in a fieldname, then it will be run for every line of the query.

1a. Put the following in a standard module for one item

Public glngMaxLen As Long

Function GetLongestValue(pstrText As String) As String
If Len(Trim$(pstrText)) > glngMaxLen Then
glngMaxLen = Len(Trim$(pstrText))
End If
GetLongestValue = pstrTtext 'Just pass it back
End Function

1b. Put following in a standard module for multiple items

Public glngMaxLen1 As Long
Public glngMaxLen2 As Long

Function GetLongestValues(pstrText1 As String, _
pstrText2 As String) As String
If Len(Trim$(pstrText1)) > glngMaxLen1 Then
glngMaxLen1 = Len(Trim$(pstrText1))
End If
If Len(Trim$(pstrText2)) > glngMaxLen2 Then
glngMaxLen2 = Len(Trim$(pstrText2))
End If
GetLongestValues = "OK" 'Just pass something back
End Function

2. Include the above in your query

Select Field1, GetLongestValue(Field1) As Dummy
From Table1, etc

Select Field1, Field2, GetLongestValue(Field1, Field2)
From Table1, etc

3. In form Load or Open event code (must be after query)

YourListBox.ColumnWidths = glngMaxLen

YourListBox.ColumnWidths = _
glngMaxLen1 & ";" & glngMaxLen2

Good Luck!

Have a great day!

j2consulting@yahoo.com

 

[VBslammer]

You can resize the columns based on their field type. This example doesn't scale the text columns down to the shortest string in the recordset, instead it uses the field size specified in the schema.

Private Sub cmdColumnSize_Click()
  Dim rst As DAO.Recordset
  Dim fld As DAO.Field
  Dim sngWidth As Single
  Dim strWidths As String
  Dim intChars As Integer
  Dim intCount As Integer
  
  Set rst = CurrentDb.OpenRecordset(Me.lstTable.RowSource)
  
  With rst
    If .RecordCount > 0 Then
      For Each fld In rst.Fields
        
        intCount = intCount + 1
      
        Select Case fld.Type
          Case dbBoolean
             intChars = 5
          Case dbByte
             intChars = 2
          Case dbInteger
             intChars = 10
          Case dbLong
             intChars = 10
          Case dbCurrency
             intChars = 14
          Case dbSingle
             intChars = 20
          Case dbDouble
              intChars = 14
          Case dbDate
             intChars = 20
          Case dbText
             intChars = fld.Size
          Case dbLongBinary
             intChars = 20
          Case dbMemo
             intChars = 255
          Case dbGUID
             intChars = 64
        End Select
        
        sngWidth = intChars * (20 * (lstTable.FontSize / 2)) / 1440
        strWidths = strWidths & sngWidth & Chr(34) & ";"
        
      Next
      
      lstTable.ColumnCount = intCount
      lstTable.ColumnWidths = strWidths
      
    End If
  End With
End Sub

VBSlammer

Unemployed in Houston, Texas

 

[SBendBuckeye]

Excellent VBS, enjoy your star!

Have a great day!

j2consulting@yahoo.com