VBA: column width adjustment sub causing "undo" of Excel to malfunction

Question

could someone please comment on the following issue? I have a sub that adjusts the column width to the text width using "AutoFit", except for column J which should have a fixed width of 80. However, I have discovered two issues with the sub:

1. The "undo" function on this working sheet is not available now.
2. Scrolling through the worksheet has become much slower, presumably because the cell width is being updated constantly.

I have decided to temporarily switch off the sub since it is not crucial. However, I am curious to know if there is any chance of getting it to work properly.

Thanks!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim ws As Worksheet
    Dim col As Long
    Dim maxCol As Long

    ' Set worksheet
    Set ws = Me

    ' Determine maximum number of columns
    maxCol = ws.Cells(1, ws.Columns.count).End(xlToLeft).Column

    ' Adjust column width
    For col = 1 To maxCol
        If col <> 10 Then ' 10 entspricht der Spalte J
            ' Adjust width of current column
            ws.Columns(col).AutoFit
        Else
            ' Set width of column J to 80
            ws.Columns(10).ColumnWidth = 80
        End If
    Next col
End Sub

Answer 1

Read the following article to understand why undo is not available after running your vba code Excel Undo vba

I also agree with @Shrotter, you should probably only run this on the WorkSheet_Change event. You could simplify like this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  
    If Target.Column = 10 Then
        Target.ColumnWidth = 80
    Else
        Target.EntireColumn.AutoFit
    End If
    
End Sub