reposition comments

[Jock]

Hi,
How can I use VBA to show comments above the cell they are attached to
rather than to the right? I have found that as the user moves accross the
spreadsheet, the comments in the right most columns disappear off the screen
until the user scrolls accross. More than a bit annoying!
Thanks,

 

[Mike H]

Jock,

All the methods for positioning comments are here and FWIW I think
positioning them centre-screen is excellent and overcomes the problem you
describe.

http://www.contextures.com/xlcomments03.html#Right

Mike

 

[Patrick Molloy]

Option Explicit
Sub AddComment()
AddCommentToCell Range("C12"), "Hello World - Left", True
AddCommentToCell Range("C13"), "Hello World - Right"
End Sub
Sub AddCommentToCell(target As Range, text As String, Optional bLEFT As
Boolean)
Dim cm As comment
Set cm = target.AddComment
With cm
.text text:=text
With .Shape
If bLEFT Then
.Left = target.Left - Range("C12").Width
End If
.Width = target.Width
End With
.Visible = True
End With
End Sub

 

[Jock]

Thanks Mike - I found an option which works well.