Making Comment Indicator Transparent

[DaveHappyNorm]

Hi all

Question relating to Comments using Excel 2010.

I have a sheet that contains a huge amount of comments making the sheet itself very difficult to read.

Is there anyway of making the comment indicator transparent?

Or

This there anyway of not displaying the comment indicator but the comment is still displayed when the user hovers over the cell?

Any ideas, settings or VB code that would help me achieve this?

Thank You.

 

[Eric W]

You don't have a lot of options here. If you go to File > Options > Advanced > Display > For cells with comments, show: you will see 3 options, none of which do exactly what you want.

Someone else figured out a way to change the color of the comment indicators. It's kind of a gimmick, by pasting a triangle over the indicator with a different color. Perhaps you can try that, and set the color to the same color as your sheet.

https://www.extendoffice.com/documents/excel/2253-excel-change-comment-indicator-color.html


Finally, you can set up a macro to cycle through the 3 options. Open your workbook, press Alt-F11 to open the VBA editor, select Insert > Module from the menu, and paste this code:

Sub TroggleCommentIndicator()
    Application.DisplayCommentIndicator = ((Application.DisplayCommentIndicator + 2) Mod 3) - 1
End Sub

Press Alt-Q to exit the editor. Now go to the Quick Access Toolbar on the upper left of Excel's window. On the right is a little dropdown arrow. Click on that, then More Commands. From Choose commands from: select Macros. Double click on TroggleCommentIndicator. Click OK. Now by repeatedly clicking on the new button in the QA menu, you can quickly switch between the 3 modes. Disable the indicators when you don't want to see them, re-enable when you do.

Not ideal, but an option. Maybe someone else has some ideas.

 

[My Aswer Is This]

Try this:
Will hide all comments on active sheet.
Will still be visible when you hover over cell.

Sub My_Comment_Hide()
'Modified  8/1/2018  6:31:20 PM  EDT
Dim cmt As Comment
    For Each cmt In ActiveSheet.Comments
        cmt.Visible = False
    Next
End Sub

 

[My Aswer Is This]

My script hides the comment. The little red indicator is still visible.
I know of no way to hide the indicator.

 

[Jaafar Tribak]

This code should display the comments when the user hovers over the cells without displaying the comment indicators :

Place the code in the ThisWorkbook Module :

Option Explicit

Private WithEvents cmbrs As CommandBars

Private Type POINTAPI
    x As Long
    Y As Long
End Type

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
    Private Declare PtrSafe Function GetCursorPos Lib "user32.dll" (lpPoint As POINTAPI) As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    Private Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If

Private Sub Workbook_Open()
    HideCommentIndicators = True
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    HideCommentIndicators = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    HideCommentIndicators = False
End Sub

Private Property Let HideCommentIndicators(ByVal Hide As Boolean)
    Application.DisplayCommentIndicator = IIf(Hide, xlNoIndicator, xlCommentIndicatorOnly)
    Set cmbrs = IIf(Hide, Application.CommandBars, Nothing)
    If Hide Then Call cmbrs_OnUpdate
End Property

Private Sub cmbrs_OnUpdate()
    Static oComment As Comment
    Dim tCurPos As POINTAPI
    
    With Application
        .CommandBars.FindControl(ID:=2020).Enabled = Not .CommandBars.FindControl(ID:=2020).Enabled
        GetCursorPos tCurPos
        On Error Resume Next
        oComment.Visible = oComment.Parent Is .ActiveWindow.RangeFromPoint(tCurPos.x, tCurPos.Y)
        Set oComment = .ActiveWindow.RangeFromPoint(tCurPos.x, tCurPos.Y).Comment
        oComment.Visible = Not .ActiveWindow.RangeFromPoint(tCurPos.x, tCurPos.Y).Comment Is Nothing
    End With
End Sub

The code should take effect after the code sets the HideCommentIndicators Property to True in the Workbook_Open event when opening the workbook.