Excel Comment: Inserting a Carriage Return

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

Running VB code to add comments to a range of cells.

The resulting comments look like this (all on one wrapped line):
"Hatters Pond Field Mobile, AL".

I'd rather the comment was on two lines:
"Hatters Pond Field
Mobile, AL"

If I was typing the comment manually, I'd add a carriage return to get
the comments on two lines. However, I'm running the code below. Any
suggestions?
Thanks, Mark

Private Sub CommandButton2_Click()
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Set sh1 = Worksheets("MB BUYERS")
Set sh2 = Worksheets("PROJ")
Dim cell As Range
For Each cell In sh1.Range("B6:B54")
On Error Resume Next
cell.Comment.Delete
On Error GoTo 0
cell.AddComment Text:=sh2.Cells(cell.Row - 4, 5) & _
" " & sh2.Cells(cell.Row - 4, 6)
cell.Select
ActiveCell.Comment.Visible = True
cell.Comment.Shape.Select True
Selection.ShapeRange.ScaleHeight 2#, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleWidth 1.22, msoFalse,
msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 0.7, msoFalse,
msoScaleFromTopLeft
cell.Select
ActiveCell.Comment.Visible = False
Next

End Sub
 
How about:

cell.AddComment Text:=sh2.Cells(cell.Row - 4, 5) & _
vblf & sh2.Cells(cell.Row - 4, 6)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA to edit a comment 3
How do I re-size a comment box using VBA? 2
Insert Comment in Current Call and set then width 1
Control the size of a Comment Box in Excel 2007 from Access 2007 1
Rescaling a Comment text-box 3
Insert Picture using Macro 10
copying a range of cells 1
Do I need a control loop to produce worksheet? 1

Back
Top