Making Comment Indicator Transparent

DaveHappyNorm

New Member
Joined
Jul 22, 2018
Messages
15
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,979
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:

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.
 
Last edited:

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,278
Try this:
Will hide all comments on active sheet.
Will still be visible when you hover over cell.
Code:
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,278
My script hides the comment. The little red indicator is still visible.
I know of no way to hide the indicator.
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,493
Office Version
2016
Platform
Windows
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 :
Code:
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.
 

Forum statistics

Threads
1,082,040
Messages
5,362,804
Members
400,693
Latest member
jenlj

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top