0

I'm trying to compare the current columnwidth to the autofit columnwidth like this:

Option Explicit
    Dim Tbl As Object
    Dim Col As Integer
    Dim I As Integer
    Dim OldColumnWidth As Integer
    Dim NewColumnWidth As Integer
Private Sub WorkSheet_Change(ByVal Target As Range)
    'On Error GoTo ErrHandler

    Set Tbl = ListObjects("RuimteTabel")
    Col = Tbl.DataBodyRange.Columns.Count
    For I = 1 To Col
        OldColumnWidth = Tbl.ListColumn(I).Range.ColumnWidth
        NewColumnWidth = Tbl.DataBodyRange.Column.AutoFit

        If NewColumnWidth < OldColumnWidth Then
            'Tbl.DataBodyRange = OldColumnWidth
        Else
            'Tbl.DataBodyRange = NewColumnWidth
        End If
    Next I

But whenever its at the OldColumnWidth = Tbl.ListColumn(I).Range.ColumnWidth line it gives the error:
Object doesn't support this property or method

I understand why the error occurs, but I do not see anything wrong with my code / how to fix the error

Nemoko
  • 421
  • 4
  • 13

1 Answers1

2

Your issue is in your syntax

OldColumnWidth = Tbl.ListColumns(I).Range.ColumnWidth

You're missing an s on the end of ListColumns.

I'd also recommend Dimming your variables as Long instead of Integer

Tom
  • 9,725
  • 3
  • 31
  • 48
  • Thanks, stupid that I didn't see that. also, doesnt an Integer take up less space compared to a long? i'll never get past 30 characters in a cell in my sheet so that's why I made it an integer for performance. – Nemoko Mar 10 '20 at 10:16
  • @Nemoko when you're looking at it for too long it becomes easy to miss the simplest things – Tom Mar 10 '20 at 10:17
  • @Nemoko - see [this question](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long) for why `Long` is preferable to `Integer`. Basically there's no performance advantage, in fact there may be a slight disadvantage. – BigBen Mar 10 '20 at 12:28