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

[zulfer7]

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

[zulfer7]

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" wrote:

>
> 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
>
>
> --
> bgeier
> ------------------------------------------------------------------------
> bgeier's Profile: http://www.excelforum.com/member.php...o&userid=12822
> View this thread: http://www.excelforum.com/showthread...hreadid=543465
>
>

[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

[zulfer7]

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" wrote:

>
> 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
>
>
> --
> bgeier
> ------------------------------------------------------------------------
> bgeier's Profile: http://www.excelforum.com/member.php...o&userid=12822
> View this thread: http://www.excelforum.com/showthread...hreadid=543465
>
>

[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

[zulfer7]

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

"bgeier" wrote:

>
> 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
>
>
> --
> bgeier
> ------------------------------------------------------------------------
> bgeier's Profile: http://www.excelforum.com/member.php...o&userid=12822
> View this thread: http://www.excelforum.com/showthread...hreadid=543465
>
>

[bgeier]

Actually, I found it most stimulating (maybe that makes me somewhat masochistic?)

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

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

This will make the entire comment Bold.

[zulfer7]

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


"bgeier" wrote:

>
> I did that intentionally to show what is the latest comment.
> If you want the whole comment to show bold, change the last line before
> the "End With" to true
>
> > .Shape.TextFrame.Characters(Len(Username) + 1).Font.Bold =
> > False --- Change to true
>
> This will make the entire comment Bold.
>
>
> --
> bgeier
> ------------------------------------------------------------------------
> bgeier's Profile: http://www.excelforum.com/member.php...o&userid=12822
> View this thread: http://www.excelforum.com/showthread...hreadid=543465
>
>