Center Comment Over Selected Cell
 

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

Center Comment Over Selected Cell
 

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

Center Comment Over Selected Cell
 

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

Center Comment Over Selected Cell
 

You're welcome!

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



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