PDA

View Full Version : Solved: Using comments to track changes



antonf
10-27-2009, 09:57 PM
Hi guys

I found the code below on this forum. As a complete noob i.r.o. VBA I don't have a clue how to change it to work for a range, a number of columns or an entire sheet.

Is there a way to hide comments requiring a user to enter a pasword to see the comments?

Your assistance will be appreciated!


Private mcPrev As Variant
Const WS_RANGE As String = "H1" '<== change to suit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address(False, False) = WS_RANGE Then

mcPrev = Target.Text
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

On Error Goto ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

If .Comment Is Nothing Then

.AddComment "Previous values" & Chr(10)
.Comment.Shape.TextFrame.Characters(1, 999).Font.Bold = True
.Comment.Shape.TextFrame.AutoSize = True
Else

.Comment.Text .Comment.Text & mcPrev & ", " & Format(Date, "m/d/yy") & "," & Environ("Username") & Chr(10)
.Comment.Shape.TextFrame.Characters(16, 999).Font.Bold = False
.Comment.Visible = False
End If
End With
End If

ws_exit:
Application.EnableEvents = True

End Sub

Bob Phillips
10-28-2009, 01:17 AM
The clue is in line 2, it tells you there to change the range to suit.

antonf
10-28-2009, 03:27 AM
Thanks for the clue, but I'm absolutely clueless as far as VBA goes.... I changed the range to "A1:E1000" for one specific sheet, but then the previous value is no longer shown in the comment.

Bob Phillips
10-28-2009, 06:16 AM
I owe you an apology, that was a bit of crappy code.

This should work better



Private mcPrev As Variant
Const WS_RANGE As String = "A1:E100" '<== change to suit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

mcPrev = Target.Text
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

If .Comment Is Nothing Then

.AddComment "Previous values" & Chr(10)
.Comment.Shape.TextFrame.Characters(1, 999).Font.Bold = True
.Comment.Shape.TextFrame.AutoSize = True
Else

.Comment.Text .Comment.Text & mcPrev & ", " & Format(Date, "m/d/yy") & "," & Environ("Username") & Chr(10)
.Comment.Shape.TextFrame.Characters(16, 999).Font.Bold = False
.Comment.Visible = False
End If
End With
End If

ws_exit:
Application.EnableEvents = True

End Sub

antonf
10-28-2009, 08:37 AM
Thanks a lot!!

How do I change the code if it is to apply to the entire sheet?

Is it possible to hide comments so that they are only viewable if someone enters a password?

lenze
10-28-2009, 06:54 PM
See my example file http://www.vbaexpress.com/kb/getarticle.php?kb_id=909

lenze

antonf
10-28-2009, 09:04 PM
Thanks a lot guys!