I usually try to avoid VBA in Excel, but it would be convenient to be able to type text into a cell, and have its column get wider or narrower to accommodate the text remaining as it's entered or deleted.
This would be subject, of course, to the lengths of the text in the other cells in the column.
'Auto-fit as you type', I guess you might call it.
Is there an easy way to do this in a suitable handler?
I'm not sure if there is a way to do it while your typing. I think excel generally stretches the cell view to display all the text before it fires the worksheet_change event.
This code will resize the column after you have changed and moved the target to a new range. Place it in the worksheet module.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim nextTarget As Range
Set nextTarget = Range(Selection.Address) 'store the next range the user selects
Target.Columns.Select 'autofit requires columns to be selected
Target.Columns.AutoFit
nextTarget.Select
End Sub
If your just looking to do it for a particular column you would need to check the target column like this:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim nextTarget As Range
Set nextTarget = Range(Selection.Address) 'store the next range the user selects
If Target.Column = 1 Then
Target.Columns.Select 'autofit requires columns to be selected
Target.Columns.AutoFit
nextTarget.Select
End If
End Sub