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
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