Show Excel Comments in Centre of Window

When you add comments to an Excel worksheet, they pop up to the top right of the cell, when you point to a cell with comments. That’s fine most of the time, but if the cell is near the top or right of the window, you might not be able to read the comment.

commentcentre01

Unfortunately, you can’t control the comment’s popup position, but with a bit of programming, you can show the comment in the centre of the screen, when you click on the cell.

commentcentre02

Centre Excel Comments Code

Paste the following code onto a worksheet module. Then, when you click on a cell that contains a comment, that comment is shown in the centre of the active window’s visible range.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 'www.contextures.com/xlcomments03.html
 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

More Excel Comment Macros

For more Excel comment macros, please visit the Excel Comment VBA page on the Contextures website.

______________

You may also like...

4 Responses

  1. Yard says:

    It’s great that you have a section on comment control using VBA – the comments concept is great, but seems dated now in terms of its execution.

    With code like that above, which runs extremely frequently, I’d tend to add in a line to check the current position of the comment before setting it. Also, the same could be done to check the value of Application.DisplayCommentIndicator before setting it repeatedly.

  2. Brian Gaines says:

    Hi, Do you have code that works when you hoover over the cell and not have to click on it. This way, I can protect the workbook.

    Thanks!

  3. Brian Gaines says:

    Thanks Debra.

Leave a Reply to Debra Dalgleish Cancel reply

Your email address will not be published. Required fields are marked *