How to hide all the comments in a selected range

[leonlai]

Hi,

I wish to write a macro that will hide (and another one that will unhide) all the comments in an Excel Sheet in a selected range (which the user selects with the mouse).

I started with a simpler example with a hard-coded range (1 cell only), and it works correctly:

Sub HideCommentsInSelection()
Dim sh As Worksheet
Dim Rng As Range


Set sh = ThisWorkbook.Sheets("Sheet1")
Set Rng = sh.Range("K16")
Rng.Select


Selection.Comment.Visible = False


End Sub

But if I use a range with many cells, like ("K16:L18"), the macro does not work.

Also, I don't want the range to be hard-coded, but allow the user to select his desired range with the mouse.

How can this be done?

I googled, but did not find a solution.

Thanks for helping
Leon

 

[DanteAmor]

Try these:

Sub [COLOR=#0000ff]Hide[/COLOR]CommentsInSelection()
    On Error Resume Next
    For Each cmt In Selection.SpecialCells(xlCellTypeComments)
        cmt.Comment.Visible = False
    Next
End Sub


Sub [COLOR=#0000ff]Unhide[/COLOR]CommentsInSelection()
    On Error Resume Next
    For Each cmt In Selection.SpecialCells(xlCellTypeComments)
        cmt.Comment.Visible = True
    Next
End Sub

 

[leonlai]

Hi, Dante Amor

Thanks a lot for your reply

Your code works like a charm.
The only thing you did not tell me is how to capture the Selection.
But I searched the net and found this:

Dim Selection As Range
Set Selection = Application.Selection

When I added these lines, the program works exactly as I wanted.

So, many thanks for your help.

-----

I wish to ask you one more question:
I am developing a big project in VB.NET which uses Excel as development platform. I'm using Add-In Express instead of VSTO (Visual Studio Tools for Office).

My first step is to develop the project in VBA, as this is easier.
Then, I will convert the VBA codes to VB.NET

I learn that the codes are very similar, since both are based on the same Excel Object Model.
Nevertheless, the conversion can be quite challenging for me (I'm not a professional programmer).

My question:
Can I ask questions on Excel using VB.NET on this forum? or is this forum only for VBA?

Best Regards,
Leon

 

[leonlai]

Hi, Dante

When I tested my code, I found that it may have some bug, because it behaves strangely.

I have 2 buttons: "Show comments" and "Hide Comments".
Here is the code for "Hide Comments".

Sub HideCommentsInSelection()
Dim sh As Worksheet
Dim SelectedRng As Range


Set sh = ThisWorkbook.Sheets("Sheet1")
Set SelectedRng = Application.Selection


On Error Resume Next


    For Each cmt In SelectedRng.SpecialCells(xlCellTypeComments)
         
            cmt.Comment.Visible = False
    Next


End Sub

If I select 2 or more cells (the selected range becomes grayed), the program behaves as expected, i.e. only the comments in the selected range are hidden or shown.

But if my cursor is on one cell (nothing selected? because the cell is not grayed), then a strange thing happens.

When I click the "Hide comments" button, ALL the comments on the whole sheet are hidden.

When I click the "Show comments" button, ALL the comments on the whole sheet are shown.

It's just as if I has selected the whole sheet before pressing a button!

It seems there is no way to select a single cell?

Could you tell me where is the problem?

Best Regards,
Leon

 

[DanteAmor]

It is not necessary to declare the selection as a range. You do not have to put it in an object, the selection is already an object.

I guess you can keep asking here about vb.net or in the general forum
https://www.mrexcel.com/forum/general-excel-discussion-other-questions/


Try this:

Sub HideCommentsInSelection()
    Dim cmt As Range
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")
    On Error Resume Next
    If Selection.Count = 1 Then
        Selection.Comment.Visible = False
    Else
        For Each cmt In Selection.SpecialCells(xlCellTypeComments)
            cmt.Comment.Visible = False
        Next
    End If
End Sub


Sub UnhideCommentsInSelection()
    Dim cmt As Range
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")
    On Error Resume Next
    If Selection.Count = 1 Then
        Selection.Comment.Visible = True
    Else
        For Each cmt In Selection.SpecialCells(xlCellTypeComments)
            cmt.Comment.Visible = True
        Next
    End If
End Sub

 

[leonlai]

Hi, Dante

I have already worked out a solution.

I noticed that the problem is not Application.Selection, which returns the correct number of cells selected (even if one).

Rather, it seems that it is SpecialCells which misbehaves when only 1 cell is selected. It behaves correctly when 2 cells are selected ... or more.

So, I reasoned:
If I concatenate the selection with another cell, say "A1", then we shall never have only 1 cell in Selection, and SpecialCells will never fail.
i.e. if I select range ("F1:F6"), it becomes ("F1:F6", "A1")
This is the case.

However, my trick is a bit untidy, and yours appears neater. Still, somebody reviewing the code may be mystified if he does not there's a bug with SpecialCells.

Your solution is different, because it avoids using SpecialCells when Selection.Count = 1, and uses it when Selection.Count is > 1
And it works fine!

So, thanks a lot, and Best Regards,
Leon

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[Tom.Jones]

Hi leonlai,

You ask

Hi,

I wish to write a macro that will hide (and another one that will unhide) all the comments in an Excel Sheet in a selected range (which the user selects with the mouse).

Then ask:

how to capture the Selection ?

The answer is mark with red in your question.