vba to remove comment indicator

[cjcass]

Hi,

Is there some code I can use that will do the following (needs to be workable for Excel 2007/10/13):

a) when a file is opened... remove all comment indicators (little red triangles) but retain all comments visible when hovering over cells

and

b) when a file is closed... re-set excel back to the norm, ie. comment indicators visible and comments visible on hovering over cells

This is for a dashboard and the red triangles look a mess - don't want to consider data validation as the cells will be locked out and not 'selectable'... and not interested in drawing mini triangles over the indicators as that's not simple workaround as I have many cells, many colours and lots of conditional formatting goin on...

Some code would be ideal... many thanks,
Chris

 

[cjcass]

any ideas yet guys..??
many thanks in anticipation...

 

[RoryA]

There is no option for that.

 

[Matt Rogers]

Hi.
I think there are only 3 options:

Sub Test1()
Application.DisplayCommentIndicator = xlNoIndicator
End Sub

Sub Test2()
Application.DisplayCommentIndicator = xlCommentIndicatorOnly
End Sub

Sub Test3()
Application.DisplayCommentIndicator = xlCommentAndIndicator
End Sub

 

[Rick Rothstein]

any ideas yet guys..??
many thanks in anticipation...

Since the cells are not selectable, you might be able to make use of this idea. Put a large ActiveX label covering all the cells that could have comments in them... make it larger than the minimum require by as much as possible and put this... and, using the Properties Window, change its BackStyle property to 0-fmBackStyleTransparent and delete its Caption, then put this in its MouseMove event...

Application.DisplayCommentIndicator = xlNoIndicator

Next, put ActiveX labels over each cell with a Comment in it and, using the Properties Window, change each one's BackStyle property to 0-fmBackStyleTransparent and delete their Captions, then put this in their MouseMove events...

Application.DisplayCommentIndicator = xlCommentAndIndicator

Now when you turn Design Mode off, moving the mouse over the cell with a comment will display the comment and moving it off the cell will hide it and its red triangle. One minor drawback... if the user clicks one of these labels, the area covered by the label will "blank out" until the mouse is moved off of it. I have not played with this idea before, but maybe you could send focus somewhere else (if you have a natural place for focus to be in your setup) inside the Click event. Anyway, it is an idea you may want to play around with. By the way, if you are familiar with Classes, I would set all of the smaller labels into a "control array" so that you only have one MouseMove event for all of them.

 

[cjcass]

Thanks for this idea Rick I will give it a go... Rory & Matt thanks also for your feedback I thought it wouldn't be just down to some easy code!!

 

[Rick Rothstein]

Thanks for this idea Rick I will give it a go...

You are welcome... I hope it works out for you. Note... make sure to put the large label down first so that all the smaller labels are "on top" of it. And, of course, let us know how it works out (or of any problems with it so that we can suggests alternates to account for them)... I, in particular, am interested in knowing if it works for you or not.