Excel VBA: Application.DisplayCommentIndicator

[leonlai]

Hi,

I understand (from surfing the net) that we can hide and show comments in Excel by using:

Application.DisplayCommentIndicator=xlCommentIndicatorOnly (to hide comments)
or
Application.DisplayCommentIndicator=xlCommentAndIndicator (to show comments)


However, this will hide or show ALL comments in the whole workbook.

Is it possible to apply these commands to a selected range only, i.e. to hide or show comments only in the small range selected by the user (using Application.Selection)?

How can this be done?

Thanks
Leon

 

[jim may]

Not sure this is exactly like you want it but...
Paste below into a Standard Module. Then Highlight the Range you want to hide all currently showing Comments and Run the Macro.
All Comments in the Range will be hidden. Afterwards, whenever you want to REINSTATE All Shown Comments Just Click (On the Review Tab)
"SHOW ALL COMMENTS".

Sub HideCommentsInSelectedRange()
Set Rng = Selection
    With Rng
        .Cells.SpecialCells(xlCellTypeComments).Select
        sAddress = .Cells.SpecialCells(xlCellTypeComments).Address
        Arr = Split(sAddress, ",")
        Icnt = UBound(Arr)
            For i = 0 To Icnt
            Range(Range(Arr(i)).Address).Comment.Visible = False
            Next i
    End With
Set Rng = Nothing
End Sub

 

[leonlai]

Hi, Jim

Thanks for your reply.

When I run the macro, the following line shows an error:

Range(Range(Arr(i)).Address.Comment.Visible = False

I am not very good at debugging. So, maybe you can tell me what's wrong.

Best Regards,
Leon

 

[jim may]

Line should be:
Range(Range(Arr(i)).Address).Comment.Visible = False

Somehow the ")" fell out following the word address.
Enter it and try again.

 

[leonlai]

Jim,

I tried again after adding the missing bracket. There is a bug on the same line.

The following Error is displayed:

Run-time error 91
Object variable or With block variable not set.

Leon

 

[jim may]

OK, one LAST TIME.. Change same full codeline to:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Range(Arr(i)).Comment.Visible = False[/FONT]