Listbox column AutoSizing class (LeBans website)

[Jerome]

Hello,

I use this class: http://www.lebans.com/listboxcolumnresize.htm to autosize the columns of my listbox. I use this listbox to populate data from my table using a textbox and a SQL statement.

The problem is this code gives a error sometimes. The strange thing is I can't reproduce it. (the error is something like object not defined or block with...unknown) The code is updated every time something in the textbox changes (so very often).

I want to add some error handling to the code, so I tried (in the autosize sub) :

on error goto ErrorNr:

ErrorNr:

err.discription

The problem is when I execute the code every time this sub is called a empty message box pops up? Is there a error in this code or am I doing something wrong?

So my questions are:

Is there anyone who is familiar with this code?

How can I built some good error handling into the code.

Thanks in advance.

 

[Jerome]

The exact errors are :

Error 94, "Invalid use off null"

Error 91, "Object variable, or with block variable not set"

Ok I fixed the cause of the errorcodes (some brackets where missing in the SQL statement. This because the table has got spaces in some of the fieldnames. I know this is not best but is is an excising db.

 

[Jerome]

Another question:

Here is a piece of code of the Autosize sub:

' Temp array to hold calculated Column Width
Dim lngArray() As Long
' Temp array to hold calculated Column Widths
Dim strArray() As String
' Temp array to hold calculated custom Column Widths
'Dim CustomStrArray As Variant

ReDim lngArray(UBound(sngWidthArray))
ReDim strArray(UBound(sngWidthArray))

For ctr = 0 To m_Control.ColumnCount - 1
    lngArray(ctr) = GetColumnMaxWidth(m_Control, ctr) + m_ColumnMargin
Next ctr

' Build the ColumnWidths property
For ctr = 0 To UBound(lngArray)
    ' Init var
    lngWidth = lngArray(ctr)
        
    If ctr <> UBound(strArray) Then
        strArray(ctr) = lngWidth & ";"
    Else
        strArray(ctr) = lngWidth
    End If
Next ctr

' Build ColumnWidths property
strTemp = ""
For ctr = 0 To UBound(strArray)
strTemp = strTemp & strArray(ctr)
Next

' Update the property
m_Control.ColumnWidths = strTemp

The problem is this code this code autosizes all the columns. usually I disable some columns by entering "0" in the column widths but now this is not so easy.

I would like to modify this code (see above).

What I would like to do is fill a array with column numbers I would like to be displayed.

For example:

Dim CustomStrArray As Variant
CustomStrArray = Array(0, 1, 4, 5, 6, 7)

if one of the column numbers is present I would like the width to become "0"

This has to be implemented somewhere inside this code (I think):

' Build the ColumnWidths property
For ctr = 0 To UBound(lngArray)
    ' Init var
    lngWidth = lngArray(ctr)
        
    If ctr <> UBound(strArray) Then
        strArray(ctr) = lngWidth & ";"
    Else
        strArray(ctr) = lngWidth
    End If
Next ctr

But how? Anyone wit(d)h some advice?

Thanks in advance.