How can we count the Hidden and Visible comments in Selection?

[leonlai]

Hi,

We can Show or Hide a comment using the Excel Menu.

Suppose I select a range on my Excel sheet. Say, there are 5 comments in that range: 4 of them are visible (Show comments) and 1 is hidden (Hide comments).

How can we write a VBA macro that will count the number of visible comments, and also the number of hidden comments?

What I am trying to do:

If there are more visible comments than hidden comments in the Selection, the macro will Hide them all.

If there are more (= or >) hidden comments in the Selection, the macro will Show them all.

So, my macro button will act as a toggle button, but what it will do depends on which type of comment is more numerous:

• If most or all comments are visible, hide them all.
• If most or all comments are hidden, show them all.

This will avoid the inconvenience of sometimes having to click the button twice to say, show all comments.

Thanks
Leon

 

[footoo]

Sub Toggle_Comments()
Dim rng As Range, cel As Range, v%, h%
On Error Resume Next
Set rng = Selection.SpecialCells(xlCellTypeComments)
On Error GoTo 0
If Not rng Is Nothing Then
    For Each cel In rng
        If cel.Comment.Visible Then v = v + 1 Else h = h + 1
    Next
    If v >= h Then
        For Each cel In rng: cel.Comment.Visible = 0: Next
    Else
        For Each cel In rng: cel.Comment.Visible = 1: Next
    End If
End If
End Sub

 

[pbornemeier]

Option Explicit

Sub ToggleComments()
    
    Dim rngComments As Range
    Dim lHidden As Long
    Dim lVisible As Long
    Dim rngCell As Range
    Dim bShow As Boolean
    
    Set rngComments = Selection.SpecialCells(xlCellTypeComments).Cells
    For Each rngCell In rngComments
        If rngCell.Comment.Visible = True Then
            lVisible = lVisible + 1
        Else
            lHidden = lHidden + 1
        End If
    Next
    If lHidden >= lVisible Then bShow = True
    For Each rngCell In rngComments
        rngCell.Comment.Visible = bShow
    Next
    
End Sub

 

[leonlai]

Hi, footoo

Thank you for your reply.

Your code works perfectly when more than 1 cell is selected.
But it gives incorrect results if only 1 cell is selected.

This is because SpecialCells(xlCellTypeComments) fails when only 1 cell is concerned. This is a bug in Excel VBA.

To make your code work perfectly, we have to cater for 2 situations:
(a) When Selection.Count >1
(b) When Selection.Count =1

I amended your code slightly, and it works like a charm!

Sub Toggle_Comments()
Dim rng As Range, cel As Range, v%, h%
On Error Resume Next
Set rng = Selection.SpecialCells(xlCellTypeComments)
On Error GoTo 0
If Not rng Is Nothing[B] And Selection.Count > 1 [/B]Then
    For Each cel In rng
        If cel.Comment.Visible Then v = v + 1 Else h = h + 1
    Next
    If v >= h Then
        For Each cel In rng: cel.Comment.Visible = 0: Next
    Else
        For Each cel In rng: cel.Comment.Visible = 1: Next
    End If
    
[B]ElseIf Not rng Is Nothing And Selection.Count = 1 Then[/B]
[B]   Selection.Comment.Visible = Not Selection.Comment.Visible[/B]
   
End If
End Sub

Best Regards,
Leon

 

[leonlai]

Hi, pbornemeier

Thank you for your reply.

Like the code provided by footoo, it works correctly, but only if >1 cell is selected.

If only 1 cell is selected, it gives incorrect results.

This is due to a bug in SpecialCells(xlCellTypeComments) which misbehaves when only 1 cell is selected.

I made a slight correction to your code (like in footoo's code), and now it works like a charm.

Best Regards,
Leon

 

[footoo]

Hi, footoo

Thank you for your reply.

Your code works perfectly when more than 1 cell is selected.
But it gives incorrect results if only 1 cell is selected.

This is because SpecialCells(xlCellTypeComments) fails when only 1 cell is concerned. This is a bug in Excel VBA.

To make your code work perfectly, we have to cater for 2 situations:
(a) When Selection.Count >1
(b) When Selection.Count =1

I amended your code slightly, and it works like a charm!

Sub Toggle_Comments()
Dim rng As Range, cel As Range, v%, h%
On Error Resume Next
Set rng = Selection.SpecialCells(xlCellTypeComments)
On Error GoTo 0
If Not rng Is Nothing[B] And Selection.Count > 1 [/B]Then
    For Each cel In rng
        If cel.Comment.Visible Then v = v + 1 Else h = h + 1
    Next
    If v >= h Then
        For Each cel In rng: cel.Comment.Visible = 0: Next
    Else
        For Each cel In rng: cel.Comment.Visible = 1: Next
    End If
    
[B]ElseIf Not rng Is Nothing And Selection.Count = 1 Then[/B]
[B]   Selection.Comment.Visible = Not Selection.Comment.Visible[/B]
   
End If
End Sub

Best Regards,
Leon

"This is because SpecialCells(xlCellTypeComments) fails when only 1 cell is concerned. This is a bug in Excel VBA."
This not a bug. It is intended to work that way.
Better to amend as follows so that a single cell gets toggled (your revision doesn't toggle a single cell) :

Sub Toggle_Comments()
Dim rng As Range, cel As Range, v%, h%
On Error Resume Next
[COLOR=#ff0000]Set rng = Intersect(Selection, Selection.SpecialCells(xlCellTypeComments))[/COLOR]
On Error GoTo 0
If Not rng Is Nothing Then
    For Each cel In rng
        If cel.Comment.Visible Then v = v + 1 Else h = h + 1
    Next
    If v >= h Then
        For Each cel In rng: cel.Comment.Visible = 0: Next
    Else
        For Each cel In rng: cel.Comment.Visible = 1: Next
    End If
End If
End Sub

 

[leonlai]

Hi, footoo
Thanks again for your comments. I will try your suggestion when I resume work on Monday.

I got plenty of problems with SpecialCells in the past, and devised my own way to bypass the problem.

Here is the problem I met with SpecialCells:
If you select 2 or more cells, it works fine.
But if you select ONLY 1 cell, it selects ALL the comments on the sheet.
This is why I thought it was a bug.

So, I am really surprised when you said that:
This is not a bug. It is intended to work that way.

Just to satisfy my curiosity, and as an aside to my main question, could you just give me an indication why Microsoft made SpecialCells choose ALL the special cells when only ONE is selected?

I cannot imagine a scenario where this odd behaviour can be useful?

Best Regards,
Leon

 

[footoo]

Just to satisfy my curiosity, and as an aside to my main question, could you just give me an indication why Microsoft made SpecialCells choose ALL the special cells when only ONE is selected?

I cannot imagine a scenario where this odd behaviour can be useful?

One way to use : If, for example, you need to go to all formula cells on a worksheet's used range, it can be done by selecting any single cell and then going to SpecialCells. (Although this could also be done by first selecting all the sheet cells and then going to SpecialCells.)

 

[leonlai]

I see!

Thanks a lot.

Leon

 

[leonlai]

Hi, footoo

I tried your amended code (using Intersect) this morning. It works perfectly.

Thanks a lot, and Best Regards,

Leon