Selected Answer
Susan
Further to Willie's good work, the Undo/ReDo buttons are ghosted since once your code modifies the Excel environment (e.g. changing column F width), the Undo buffer is flushed.
Replacement Answer 16 March 2024:
After I'd posted a revised Answer (now deleted) based on automatic expansion but shrinking the column via a button (a bit like Wille's approach), I realised that a better compromise is possible...
In the attached file, column F expands and shrinks automatically but Undo is available mainly- it is only lost when you first click inside or first click outside the specified range. When you're inside the range, you can Undo actions- and the same if you're just working outside that range. The revised code is:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ExpWidth As Long, ExpRng As Range, ShrnkWidth As Long
' state desired expanded and shrunken widths...
ExpWidth = 70
ShrnkWidth = 35
' ... of column if cells are selected in this single column range
Set ExpRng = Range("F18:F22")
' do nothing for multiple selections...
If Target.Count <> 1 Then Exit Sub
' only adjust column if one specified cells is FIRST selected
If Not Intersect(Target, ExpRng) Is Nothing And ExpRng.ColumnWidth = ShrnkWidth Then
'expand specified range
Columns(ExpRng.Column).ColumnWidth = ExpWidth
' reselect (so NameBox repopulates)
Target.Select
End If
' only restore column if a cell outside specified range is FIRST selected
If Intersect(Target, ExpRng) Is Nothing And ExpRng.ColumnWidth = ExpWidth Then
' shrink specifed range
Columns(ExpRng.Column).ColumnWidth = ShrnkWidth
' reselect (so NameBox repopulates)
Target.Select
End If
End Sub
where you may need to change the values in bold (in other cases).
Once the column is expanded, the macro does nothing (so the Undo buffer isn't flushed) and likewise if it's already shrunken.
Incidentally, Willie pointed out correctly that the cell address (in the formula bar's NameBox) disappears very quickly when the original Worksheet_SelectionChange code is operational (and changes the environment) but I found that can be fixed easily via the lines above like:
' reselect (so NameBox repopulates)
Target.Select
Hope this helps.