Expert Zone
XLSX + XLS repair tool

A User Defined Function to Review All Comments

By

Bernd Held

Cells with comments have red indicator triangles in the upper-right corners, usually the comments are hidden but appear if the mouse pointer is rested over a particular cell.

It is also possible to hide the red indicator. The traditional way to review all comments would be by clicking Comments in the View menu. Of course it is also possible to create a user defined function that will display "True" when a comment is found.

To check if a cell contains a comment:

  • Press <Alt + F11> to open the Visual Basic Editor.
  • Insert module
  • Type the following function.
Function ComT(rng As Range)
On Error GoTo errorM
If Len(rng.Comment.Text) 0 Then ComT = True
Exit Function
errorM:
ComT = False
End Function
  • Close the VBA-Editor by pressing <Alt + Q>.
  • Select cells C1:C5.
  • Type the formula =ComT(A1) .
  • Press <Ctrl + Enter>.

If there are comments in cells A1:A5 it shows the value true in the concerning cell.