Automatically updating row height upon cell selection (without losing undo stack)

[coraltea]

RickXL posted a great code that uses vba to change the row height of the selected row. See Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static iRow As Long
If Target.Rows.Count > 1 Then Exit Sub
If iRow > 0 Then
Rows(iRow).Rowheight = 20
End If
iRow = Target.Row
Target.RowHeight = 40
End Sub

I essentially just use this code to make the selected record more readable due to the long entries that some of the cells contain. The only drawback from this code is that it clears the undo stack from excel so any time that I have a selection change it is permanent. Is there a work around that would allow me to keep the undo stack?

 

[Larry Haydn]

Would formatting the cells to wrap text give you the same result?

 

[coraltea]

The cells are already formatted to wrap the text. The column widths must remain the same which isn't very wide. I could just have all the rows expanded really large but that makes the spread sheet very clunky and difficult to scroll through with the standard 3 row scroll. Being able to keep all the rows that I'm not looking at standard height while expanding the current row would help with 1) reading record entries and 2) tracking which record I'm editing as the table can be quite large.

 

[coraltea]

Is there something that I can provide to help clarify the question? I'm not getting any responses.

 

[MARK858]

You're probably not getting any responses because when VBA interacts with a sheet it automatically wipes out the Undo stack.

 

[Fluff]

Unfortunately, I think you are stuck with it.
Either use the macro & loose the stack, or keep the stack & don't use the macro.

 

[Yongle]

The question is perfectly clear
Unfortunately running VBA, the way you require, kills undo and that's it

 

[coraltea]

I was afraid of that. Thanks for the quick responses. I'll have to try to find some other work around.