PDA

View Full Version : Need help with Auto Comment macro



davidw
06-16-2011, 08:35 PM
I have a macro that is worksheet activated. It works great and has for a long time now, however whenever i edit the comment with BOLD or color it reverts back to standard text the next time the comment is activated. Ive attached the file. Could someone please help. Thank you.

davidw
06-25-2011, 12:08 PM
Can anyone help?

Paul_Hossler
06-25-2011, 04:28 PM
I'm not sure about all your logic, but it seems that your


OldComment = .Comment.Text
.ClearComments ' <------


is also clearing the formating

Maybe ...


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

'copy previous value to another sheet
'create another worksheet to put data on and hide it
Sheet2.Range(Target.Address) = Target.Text

Const COMMENT_HEAD As String = "ITEM HISTORY BELOW"
'====================================
Dim strDate As String
Dim strTime As String
Dim strOldComment As String

strDate = date
strTime = Time
'====================================
'//clearing more than one cell causes an error which will allow you to delete

On Error Resume Next
'//(it will not overwrite an existing comment but will add after)
With Target

strOldComment = .Comment.Text

If Len(strOldComment) = 0 Then
.AddComment
.Comment.Visible = False
.Comment.Text Text:=COMMENT_HEAD & vbNewLine _
& Sheet2.Range(.Address) & " " & strDate & " " & Target.EntireRow.Range("b1")
Else
.Comment.Text Text:=strOldComment & vbNewLine _
& Sheet2.Range(.Address) & " " & strDate & " " & Target.EntireRow.Range("b1")
End If

End With
Application.Run "'03-27-09 comment program to track changes.xls'!Comments_AutoSize"
End Sub


Paul

davidw
07-01-2011, 03:59 PM
That didnt help. It appears that the 1st text in the comment determines what they all will be. Example: If I change the first letter in the comment to Bold Blue then when it regenerates it makes everything bold blue.