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.
Move Comments with Macro
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.
______________
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.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!
@Brian, if you allow clicking on locked cells, the code should work on a protected sheet. I don’t have any code that would run when you hover over the cell.
Thanks Debra.