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.
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.
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.