software

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 = quot;ddmmmyy hh:mmquot;
Username = Application.Username
Set cmt = ActiveCell.Comment
lName = 0

If cmt Is Nothing Then
Set cmt = ActiveCell.AddComment
cmt.Text Text:=Username amp; quot; quot; amp; Format(Now, strDate) amp; 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 amp; Chr(10) _
amp; Username amp; 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


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

strDate = quot;ddmmmyy hh:mmquot;
Username = application.Username
Set cmt = ActiveCell.Comment
lName = 0

If cmt Is Nothing Then
Set cmt = ActiveCell.AddComment
With cmt
.Text (Username amp; quot; quot; amp; Format(Now, strDate) amp; Chr(10))
.Shape.TextFrame.Characters(1, Len(Username)).Font.Bold =
True
End With
Else
Set cmt = ActiveCell.Comment
With cmt
.Text (quot;quot;)
.Shape.TextFrame.Characters(1, Len(cmt.Text)).Font.Bold =
False
.Text (Username)
.Shape.TextFrame.Characters(1, Len(Username)).Font.Bold = True
.Text (cmt.Text amp; quot; quot; amp; Chr(10) amp; Format(Now, strDate))
.Shape.TextFrame.Characters(Len(Username) 1,
Len(strDate)).Font.Bold = True
End With
End If
End Sub--
bgeier
------------------------------------------------------------------------
bgeier's Profile: www.excelforum.com/member.php...oamp;userid=12822
View this thread: www.excelforum.com/showthread...hreadid=543465This 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.

quot;bgeierquot; wrote:

gt;
gt; This worked on my machine
gt; Sub KeyCellsChanged()
gt; Dim strDate As String
gt; Dim cmt As Comment
gt; Dim Username As String
gt; Dim lName As Long
gt;
gt; strDate = quot;ddmmmyy hh:mmquot;
gt; Username = application.Username
gt; Set cmt = ActiveCell.Comment
gt; lName = 0
gt;
gt; If cmt Is Nothing Then
gt; Set cmt = ActiveCell.AddComment
gt; With cmt
gt; .Text (Username amp; quot; quot; amp; Format(Now, strDate) amp; Chr(10))
gt; .Shape.TextFrame.Characters(1, Len(Username)).Font.Bold =
gt; True
gt; End With
gt; Else
gt; Set cmt = ActiveCell.Comment
gt; With cmt
gt; .Text (quot;quot;)
gt; .Shape.TextFrame.Characters(1, Len(cmt.Text)).Font.Bold =
gt; False
gt; .Text (Username)
gt; .Shape.TextFrame.Characters(1, Len(Username)).Font.Bold = True
gt; .Text (cmt.Text amp; quot; quot; amp; Chr(10) amp; Format(Now, strDate))
gt; .Shape.TextFrame.Characters(Len(Username) 1,
gt; Len(strDate)).Font.Bold = True
gt; End With
gt; End If
gt; End Sub
gt;
gt;
gt; --
gt; bgeier
gt; ------------------------------------------------------------------------
gt; bgeier's Profile: www.excelforum.com/member.php...oamp;userid=12822
gt; View this thread: www.excelforum.com/showthread...hreadid=543465
gt;
gt;


I inadvertantly set the last quot;Font.Boldquot; 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 = quot;ddmmmyy hh:mmquot;
Username = application.Username
Set cmt = ActiveCell.Comment
lName = 0

If cmt Is Nothing Then
Set cmt = ActiveCell.AddComment
With cmt
.Text (Username amp; quot; quot; amp; Format(Now, strDate) amp; 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 (quot;quot;)
.Text (Username)
.Shape.TextFrame.Characters(1, Len(Username)).Font.Bold = True
.Text (cmt.Text amp; quot; quot; amp; Chr(10) amp; 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: www.excelforum.com/member.php...oamp;userid=12822
View this thread: www.excelforum.com/showthread...hreadid=543465Thanks 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.

quot;bgeierquot; wrote:

gt;
gt; I inadvertantly set the last quot;Font.Boldquot; statement to true! I hate that
gt; when that happens. It should work now.
gt; Sorry about the inconvenience.
gt;
gt; Sub KeyCellsChanged()
gt; Dim strDate As String
gt; Dim cmt As Comment
gt; Dim Username As String
gt; Dim lName As Long
gt;
gt; strDate = quot;ddmmmyy hh:mmquot;
gt; Username = application.Username
gt; Set cmt = ActiveCell.Comment
gt; lName = 0
gt;
gt; If cmt Is Nothing Then
gt; Set cmt = ActiveCell.AddComment
gt; With cmt
gt; .Text (Username amp; quot; quot; amp; Format(Now, strDate) amp; Chr(10))
gt; .Shape.TextFrame.Characters(1, Len(Username)).Font.Bold =
gt; True
gt; End With
gt; Else
gt; Set cmt = ActiveCell.Comment
gt; With cmt
gt; .Shape.TextFrame.Characters(1, Len(cmt.Text)).Font.Bold =
gt; False
gt; .Text (quot;quot;)
gt; .Text (Username)
gt; .Shape.TextFrame.Characters(1, Len(Username)).Font.Bold = True
gt; .Text (cmt.Text amp; quot; quot; amp; Chr(10) amp; Format(Now, strDate))
gt; .Shape.TextFrame.Characters(Len(Username) 1, Len(strDate)
gt; 2).Font.Bold = False
gt; End With
gt; End If
gt; End Sub
gt;
gt;
gt; --
gt; bgeier
gt; ------------------------------------------------------------------------
gt; bgeier's Profile: www.excelforum.com/member.php...oamp;userid=12822
gt; View this thread: www.excelforum.com/showthread...hreadid=543465
gt;
gt;


Try this

Option Explicit

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

strDate = quot;ddmmmyy hh:mmquot;
Username = Application.Username
Set cmt = ActiveCell.Comment

If cmt Is Nothing Then
Set cmt = ActiveCell.AddComment
With cmt
.Text (Username amp; quot; quot; amp; Format(Now, strDate) amp; Chr(10))
.Shape.TextFrame.Characters(1, Len(Username)).Font.Bold =
True
End With
Else
Set cmt = ActiveCell.Comment
strCommentText = cmt.Text
With cmt
.Text (quot;quot;)
.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 amp; quot; quot; amp; Format(Now(), strDate)) amp; Chr(10) amp;
strCommentText
.Shape.TextFrame.Characters(Len(Username) 1).Font.Bold =
False
End With
End If
End Sub--
bgeier
------------------------------------------------------------------------
bgeier's Profile: www.excelforum.com/member.php...oamp;userid=12822
View this thread: www.excelforum.com/showthread...hreadid=543465bgeier, now it only formats the first instance of username bold. Isn't
this particular code a pain.

quot;bgeierquot; wrote:

gt;
gt; Try this
gt;
gt; Option Explicit
gt;
gt; Sub KeyCellsChanged()
gt; Dim strDate As String
gt; Dim cmt As Comment
gt; Dim Username As String
gt; Dim strCommentText As String
gt;
gt; strDate = quot;ddmmmyy hh:mmquot;
gt; Username = Application.Username
gt; Set cmt = ActiveCell.Comment
gt;
gt; If cmt Is Nothing Then
gt; Set cmt = ActiveCell.AddComment
gt; With cmt
gt; .Text (Username amp; quot; quot; amp; Format(Now, strDate) amp; Chr(10))
gt; .Shape.TextFrame.Characters(1, Len(Username)).Font.Bold =
gt; True
gt; End With
gt; Else
gt; Set cmt = ActiveCell.Comment
gt; strCommentText = cmt.Text
gt; With cmt
gt; .Text (quot;quot;)
gt; .Shape.TextFrame.Characters(1, Len(cmt.Text)).Font.Bold =
gt; False
gt; .Text (Username)
gt; .Shape.TextFrame.Characters(1, Len(Username) - 1).Font.Bold =
gt; True
gt; .Text (cmt.Text amp; quot; quot; amp; Format(Now(), strDate)) amp; Chr(10) amp;
gt; strCommentText
gt; .Shape.TextFrame.Characters(Len(Username) 1).Font.Bold =
gt; False
gt; End With
gt; End If
gt; End Sub
gt;
gt;
gt; --
gt; bgeier
gt; ------------------------------------------------------------------------
gt; bgeier's Profile: www.excelforum.com/member.php...oamp;userid=12822
gt; View this thread: www.excelforum.com/showthread...hreadid=543465
gt;
gt;


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 quot;End Withquot; to true

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

This will make the entire comment Bold.--
bgeier
------------------------------------------------------------------------
bgeier's Profile: www.excelforum.com/member.php...oamp;userid=12822
View this thread: www.excelforum.com/showthread...hreadid=543465I don't want the entire comment bold, just the user name in each successive
line anytime a change is made to the cell.
EXAMPLE
lt;BOLDgt;Chadlt;/BOLDgt; dd/mmm/yy hh:mm
Comment note

lt;BOLDgt;Chadlt;/BOLDgt; dd/mmm/yy hh:mm
2nd Comment note

etcquot;bgeierquot; wrote:

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