undo command grayed out

@Susan

I don't like to walk away feeling beaten, so I did some more digging. First I added another worksheet (PO Form 2) and formatted rows and columns like the original PO Form. I then, one-by-one, added the macros to that sheet. Everything was fine until adding the Worksheet_SelectionChange event. That's when things went off the rails. 1) The reason the "UnDO - ReDo" or Ctrl+Z are not available is because changes made by a macro (Worksheet_SelectionChange event) have no UnDo ability. 2) The cell name issue also cropped up when this event was added. I have been unable to find a way do the column re-size and have the cell name show without having to hover the cursor over the down arrow of the name box. On my "PO Form 2" sheet I added a button with a macro to resize col "F" as/when needed. (file attached so you can see the result)

I also recommend getting rid of the class module since it is empty and sheet1 does not exist. I would also put all of the macros in a regular module and just leave the Worksheet_SelectionChange event in the worksheet code window.

If this answers your question please mark my answer as Selected.

Cheers   :-)

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.