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

 

[My Aswer Is This]

I see your still working with comments. Remember me? I helped you about two weeks ago putting all your comments in a TextBox.

Are you still using that or have you decided on some other way of doing things?
Just curious.

 

[leonlai]

Hi, My Aswer Is This

How do you do?

Yes, I am still working on a few menus in comments (they are the last ones!). I agree I am a bit slow. I am not a programmer, but an accountant!

I have taken good note of all your very helpful suggestions, and am implementing them in my project.

I am still sticking on my way of doing things, namely getting all the VBA codes first (because this approach is easier), then translating them to VB. NET code and Add-In Express, using Visual Studio.

I am doing a big company-wide project, and I am working very hard to make it a success.

Best Regards,
Leon

PS:
My next topics will probably be on making connection with SQL Server using ADO.NET, using Excel VBA
I know a little about this, but probably will need forum help to get professional solutions.