Excel VBA - How to remove the extra column in ListView

Question

Dear All,

I'm using a ListView to store the data of a recordset I have created.

Everything is working fine but I noticed I got an extra column at the end (see the red selection in the first below image):

Any way to remove it?

Any property can it be set?

This is the actual behavior:

What I need:

Thanks

Answer 1

Looking closer at that picture, I don't think that is a column at all, I think your control is wider than your total column widths.

Answer 2

It's not really a good idea to use a class name as a variable name, as it makes your code less clear, but I suspect you just need to clear the column headers first:

Public Sub ListView_Header(ListView As ListView, Rst As ADODB.Recordset)
    Dim i As Integer
    ListView.ColumnHeaders.Clear
    For i = 0 To Rst.Fields.Count - 1
        ListView.ColumnHeaders.Add , , Rst.Fields.Item(i).Name, ListView.Width
    Next i
    ListView.View = lvwReport
End Sub

Answer 3

It's not really a good idea to use a class name as a variable name, as it makes your code less clear, but I suspect you just need to clear the column headers first:

Public Sub ListView_Header(ListView As ListView, Rst As ADODB.Recordset)
    Dim i As Integer
    ListView.ColumnHeaders.Clear
    For i = 0 To Rst.Fields.Count - 1
        ListView.ColumnHeaders.Add , , Rst.Fields.Item(i).Name, ListView.Width
    Next i
    ListView.View = lvwReport
End Sub

Thanks!

This is OK but it is not solving my issue in fact I still have an additional column at the end of the table

Answer 4

Looking closer at that picture, I don't think that is a column at all, I think your control is wider than your total column widths.

You are right and thanks to your comment I have changed my code to:

Public Sub AutoResizeListView(MyListView As Variant, Userfrm As MSForms.UserForm)
    Dim TempLabel As Object
    Dim MaxColumnWidth As Double
    Dim TempLastColumnWidth As Double
    Dim SumMaxColumnWidth As Double
    Dim i As Integer
    Dim j As Integer

    Set TempLabel = Userfrm.Controls.Add("Forms.Label.1", "Test Label", True)
    With TempLabel
        .Font.Size = MyListView.Font.Size
        .Font.Name = MyListView.Font.Name
        .WordWrap = False
        .AutoSize = True
        .Visible = False
    End With
    TempLabel.Caption = MyListView.ColumnHeaders(1).Text
    MaxColumnWidth = TempLabel.Width
    For i = 1 To MyListView.ListItems.Count
        TempLabel.Caption = MyListView.ListItems(i).Text
        If TempLabel.Width > MaxColumnWidth Then
            MaxColumnWidth = TempLabel.Width
        End If
    Next i
    MyListView.ColumnHeaders(1).Width = MaxColumnWidth + 8
    SumMaxColumnWidth = MyListView.ColumnHeaders(1).Width
    For i = 1 To MyListView.ColumnHeaders.Count - 1
        TempLabel.Caption = MyListView.ColumnHeaders(i + 1).Text
        MaxColumnWidth = TempLabel.Width
        For j = 1 To MyListView.ListItems.Count
            TempLabel.Caption = MyListView.ListItems(j).SubItems(i)
            If TempLabel.Width > MaxColumnWidth Then
                MaxColumnWidth = TempLabel.Width
            End If
        Next j
        If (i + 1) = MyListView.ColumnHeaders.Count Then
            TempLastColumnWidth = MaxColumnWidth + 8
            SumMaxColumnWidth = SumMaxColumnWidth + TempLastColumnWidth
        Else
            MyListView.ColumnHeaders(i + 1).Width = MaxColumnWidth + 8
            SumMaxColumnWidth = SumMaxColumnWidth + MyListView.ColumnHeaders(i + 1).Width
        End If
        If SumMaxColumnWidth < MyListView.Width And (i + 1) = MyListView.ColumnHeaders.Count Then
            MyListView.ColumnHeaders(i + 1).Width = (MyListView.Width - SumMaxColumnWidth) + TempLastColumnWidth
        Else
            MyListView.ColumnHeaders(i + 1).Width = MaxColumnWidth + 8
        End If
    Next i
    Userfrm.Controls.Remove TempLabel.Name
End Sub