Macro to show all comment ticks

[Zimmerman]

I have a sheet that automatically inserts a comment in each cell that the user has changed. The comment keeps track of the last three value changes. What I am wanting to do is create a macro that the user can click a button and then the little red tick mark for a comment will appear on each cell that has one. Then click it again to hide all comments. Can anybody help me out with this macro? Below is the VBA code that inserts the comments.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim CellComment As Comment
Dim CommentText As String
Dim aryVals As Variant
Dim i As Long
If Target.Count = 1 Then
Set CellComment = Target.Comment
If Not CellComment Is Nothing Then
If InStr(1, CellComment.Text, "User: ") > 0 Then
If InStr(1, Mid(CellComment.Text, InStr(1, CellComment.Text, ":") + 1), ",") Then
aryVals = Split(Mid(CellComment.Text, InStr(1, CellComment.Text, ":") + 1), ",")
Select Case Len(CellComment.Text) - Len(Replace(CellComment.Text, ",", vbNullString))
Case 1
aryVals = Array(Environ("Username ") & Chr(32) & "entered " & Chr(32) & Target.Value & Chr(32) & "on " & Format(Now(), "mm/dd/yy"), _
aryVals(0), _
aryVals(1))
Case 2
For i = UBound(aryVals) To LBound(aryVals) + 1 Step -1
aryVals(i) = aryVals(i - 1)
Next
aryVals(LBound(aryVals)) = Environ("Username ") & Chr(32) & "entered " & Chr(32) & Target.Value & Chr(32) & "on " & Format(Now(), "mm/dd/yy")
Case Else
Exit Sub
End Select
CellComment.Text "User: " & Join(aryVals, ",")
Else
CellComment.Text "User: " & Environ("Username ") & Chr(32) & "entered " & Chr(32) & Target.Value & Chr(32) & "on " & Format(Now(), "mm/dd/yy") & ", " & _
Mid(CellComment.Text, InStr(1, CellComment.Text, ":") + 1)
End If
End If
Else
Target.AddComment "User: " & Environ("Username ") & Chr(32) & "entered " & Chr(32) & Target.Value & Chr(32) & _
"on " & Format(Now(), "mm/dd/yy")
End If
End If
End Sub


Thanks
Excel 2003
Windows XP

 

[RoryA]

Basically:

Application.DisplayCommentIndicator = xlNoIndicator

and:

Application.DisplayCommentIndicator = xlCommentIndicatorOnly

 

[Zimmerman]

Easy enough. Thank you very much!

 

[Zimmerman]

Do you know if there's a way to color code the text in a comment box based on the usersname?

 

[RoryA]

You have to specify the characters in the text you want coloured (by start position and number of characters)

activecell.Comment.Shape.TextFrame.Characters(1, 4).Font.ColorIndex = 3

for example.

 

[Zimmerman]

How would this work for each user that uses this? Would i need to specify a color for each user name or something?

 

[RoryA]

Yes. Unless you have magical powers.

 

[Zimmerman]

Nope i can't say i have any special powers. And i know this might sound a little dumb, but how would i put that in for each user? There's only 5 different users that will be editing this so putting in a color for each user won't be that big of deal. I just don't know how to do it.

 

[RoryA]

Simplest way would be a select case statement:

Select case environ("username")
case "bob"
  lngColourIndex = 3
Case "mike"
   lngcolourindex = 2
Case else
   lngcolourindex = 4
End Select
activecell.Comment.Shape.TextFrame.Characters(1, 4).Font.ColorIndex = lngcolourindex

for example.