Comment box compression

[Otto Moehrbach]

I have a file of numerous records (one row per record). I have a macro
assigned to a Forms button that copies & transposes the active row into a
View sheet for viewing & editing. Another macro & Forms button on the View
sheet copies & transposes the record back to its original location. All
works well.
Many of the cells contain code text that I copied direct from code
modules, others have text I typed in. For this post I'll call them code
cells and text cells.
The problem is with comment boxes. Comment boxes in text cells behave
normal. Comment boxes in code cells are compressed, left to right, into a
single vertical line. I can expand these boxes to appear normal but when I
cycle the copy/transpose action from one sheet to another, the comment boxes
revert back to single vertical lines.
Could the cells be corrupted? How can the cells be repaired? What can
I do in the future when I copy/paste code to cells to keep this from
happening? Thanks for your help. Otto

[Dave Peterson]

I've always found that comment boxes pretty much do what they want. But maybe
you could resize them after you're done copying|Pasting:

Option Explicit

Sub testme()

Dim mycell As Range
Dim myRng As Range
Dim lArea As Long

Set myRng = Selection

For Each mycell In myRng.Cells
If Not (mycell.Comment Is Nothing) Then
With mycell.Comment
.Shape.TextFrame.AutoSize = True
If .Shape.Width > 300 Then
lArea = .Shape.Width * .Shape.Height
.Shape.Width = 200
.Shape.Height = (lArea / 200) * 1.2
End If
End With
End If
Next mycell

End Sub

--

Dave Peterson
ec3...@msn.com

[Otto Moehrbach]

Dave
Thank you for responding. That's a nice little procedure to have. I'll
look into putting it into my Copy/Paste/Transpose procedures. Otto
"Dave Peterson" <ec3...@msn.com> wrote in message
news:3C7ABF50...@msn.com...