Please help a newbie with an excel vba macro relating to comments.

[Guest]

Can you help me by editing this code so that every instance of the username
is in bold whether it is a new comment or an add to comment? I would prefer
just the username be in bold but the username,hour,time line being all bold
as it is now is acceptable. I tried just about everything I can think of so
far so HELP!

Sub KeyCellsChanged()
Dim strDate As String
Dim cmt As Comment
Dim Username As String
Dim lName As Long

strDate = "ddmmmyy hh:mm"
Username = Application.Username
Set cmt = ActiveCell.Comment
lName = 0

If cmt Is Nothing Then
Set cmt = ActiveCell.AddComment
cmt.Text Text:=Username & " " & Format(Now, strDate) & Chr(10)
lName = InStr(1, cmt.Text, Chr(10)) - 1
cmt.Shape.TextFrame.Characters(1, lName).Font.Bold = True
Else
cmt.Text Text:=cmt.Text & Chr(10) _
& Username & Format(Now, strDate)
lName = InStr(1, cmt.Text, Chr(10)) - 1
cmt.Shape.TextFrame.Characters.Font.Bold = False
cmt.Shape.TextFrame.Characters(1, lName).Font.Bold = True
End If

End Sub

 

[bgeier]

This worked on my machine
Sub KeyCellsChanged()
Dim strDate As String
Dim cmt As Comment
Dim Username As String
Dim lName As Long

strDate = "ddmmmyy hh:mm"
Username = application.Username
Set cmt = ActiveCell.Comment
lName = 0

If cmt Is Nothing Then
Set cmt = ActiveCell.AddComment
With cmt
..Text (Username & " " & Format(Now, strDate) & Chr(10))
..Shape.TextFrame.Characters(1, Len(Username)).Font.Bold =
True
End With
Else
Set cmt = ActiveCell.Comment
With cmt
..Text ("")
..Shape.TextFrame.Characters(1, Len(cmt.Text)).Font.Bold =
False
..Text (Username)
..Shape.TextFrame.Characters(1, Len(Username)).Font.Bold = True
..Text (cmt.Text & " " & Chr(10) & Format(Now, strDate))
..Shape.TextFrame.Characters(Len(Username) + 1,
Len(strDate)).Font.Bold = True
End With
End If
End Sub

 

[Guest]

This worked perfectly when adding a new comment, but after changing the cell
again it turned the entire comment bold. This macro begins with a Auto Run
macro and then a macro that determines if any cell in the range changes to
add an addition to the comment, thats what the Else in this macro is for, I
think it still needs a little tweaking.

 

[bgeier]

I inadvertantly set the last "Font.Bold" statement to true! I hate that
when that happens. It should work now.
Sorry about the inconvenience.

Sub KeyCellsChanged()
Dim strDate As String
Dim cmt As Comment
Dim Username As String
Dim lName As Long

strDate = "ddmmmyy hh:mm"
Username = application.Username
Set cmt = ActiveCell.Comment
lName = 0

If cmt Is Nothing Then
Set cmt = ActiveCell.AddComment
With cmt
..Text (Username & " " & Format(Now, strDate) & Chr(10))
..Shape.TextFrame.Characters(1, Len(Username)).Font.Bold =
True
End With
Else
Set cmt = ActiveCell.Comment
With cmt
..Shape.TextFrame.Characters(1, Len(cmt.Text)).Font.Bold =
False
..Text ("")
..Text (Username)
..Shape.TextFrame.Characters(1, Len(Username)).Font.Bold = True
..Text (cmt.Text & " " & Chr(10) & Format(Now, strDate))
..Shape.TextFrame.Characters(Len(Username) + 1, Len(strDate) +
2).Font.Bold = False
End With
End If
End Sub

 

[Guest]

Thanks bgeier for your assistance, the code does not keep the old text, the
ELSE statement should leave the comment as it is but add on to it, this code
works perfectly except for not adding to the existing comment, it overwrites
the old comment.

 

[bgeier]

Try this

Option Explicit

Sub KeyCellsChanged()
Dim strDate As String
Dim cmt As Comment
Dim Username As String
Dim strCommentText As String

strDate = "ddmmmyy hh:mm"
Username = Application.Username
Set cmt = ActiveCell.Comment

If cmt Is Nothing Then
Set cmt = ActiveCell.AddComment
With cmt
..Text (Username & " " & Format(Now, strDate) & Chr(10))
..Shape.TextFrame.Characters(1, Len(Username)).Font.Bold =
True
End With
Else
Set cmt = ActiveCell.Comment
strCommentText = cmt.Text
With cmt
..Text ("")
..Shape.TextFrame.Characters(1, Len(cmt.Text)).Font.Bold =
False
..Text (Username)
..Shape.TextFrame.Characters(1, Len(Username) - 1).Font.Bold =
True
..Text (cmt.Text & " " & Format(Now(), strDate)) & Chr(10) &
strCommentText
..Shape.TextFrame.Characters(Len(Username) + 1).Font.Bold =
False
End With
End If
End Sub

 

[Guest]

bgeier, now it only formats the first instance of username bold. Isn't
this particular code a pain.

 

[bgeier]

I did that intentionally to show what is the latest comment.
If you want the whole comment to show bold, change the last line befor
the "End With" to true

.Shape.TextFrame.Characters(Len(Username) + 1).Font.Bold =
False --- Change to true

This will make the entire comment Bold

 

[Guest]

I don't want the entire comment bold, just the user name in each successive
line anytime a change is made to the cell.
EXAMPLE
<BOLD>Chad</BOLD> dd/mmm/yy hh:mm
Comment note

<BOLD>Chad</BOLD> dd/mmm/yy hh:mm
2nd Comment note

etc