I found this useful bit of code on
http://www.contextures.com/xlcomments03.html
With this code, comments are displayed in the center of the active
window's visible range. I have tried to modify this code to center the
comments over the selected cell. I am not very good with VB in Excel,
and I have not been able to make the necessary modification. Can
someone please give me the code that would allow me to do what I want.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim rng As Range
Dim cTop As Long
Dim cWidth As Long
Dim cmt As Comment
Dim sh As Shape

Application.DisplayCommentIndicator = xlCommentIndicatorOnly

Set rng = ActiveWindow.VisibleRange
cTop = rng.Top + rng.Height / 2
cWidth = rng.Left + rng.Width / 2

If ActiveCell.Comment Is Nothing Then
'do nothing
Else
Set cmt = ActiveCell.Comment
Set sh = cmt.Shape
sh.Top = cTop - sh.Height / 2
sh.Left = cWidth - sh.Width / 2
cmt.Visible = True
End If

End Sub

If you change the range reference to the active cell, it should centre
the comment over that cell:

Set rng = ActiveCell


inthepickle wrote:
> I found this useful bit of code on
> http://www.contextures.com/xlcomments03.html
> With this code, comments are displayed in the center of the active
> window's visible range. I have tried to modify this code to center the
> comments over the selected cell. I am not very good with VB in Excel,
> and I have not been able to make the necessary modification. Can
> someone please give me the code that would allow me to do what I want.
>
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>
> Dim rng As Range
> Dim cTop As Long
> Dim cWidth As Long
> Dim cmt As Comment
> Dim sh As Shape
>
> Application.DisplayCommentIndicator = xlCommentIndicatorOnly
>
> Set rng = ActiveWindow.VisibleRange
> cTop = rng.Top + rng.Height / 2
> cWidth = rng.Left + rng.Width / 2
>
> If ActiveCell.Comment Is Nothing Then
> 'do nothing
> Else
> Set cmt = ActiveCell.Comment
> Set sh = cmt.Shape
> sh.Top = cTop - sh.Height / 2
> sh.Left = cWidth - sh.Width / 2
> cmt.Visible = True
> End If
>
> End Sub
>


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Thanks Debra. I knew it would be something really simple.

You're welcome!

inthepickle wrote:
> Thanks Debra. I knew it would be something really simple.
>


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html