Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help a newbie with an excel vba macro relating to comments.
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help a newbie with an excel vba macro relating to comments.
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help a newbie with an excel vba macro relating to comme
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help a newbie with an excel vba macro relating to comments.
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help a newbie with an excel vba macro relating to comme
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help a newbie with an excel vba macro relating to comments.
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help a newbie with an excel vba macro relating to comme
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help a newbie with an excel vba macro relating to comments.
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help a newbie with an excel vba macro relating to comme
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 <BOLDChad</BOLD dd/mmm/yy hh:mm Comment note <BOLDChad</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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Macro - change printer | Excel Discussion (Misc queries) | |||
Closing File Error | Excel Discussion (Misc queries) | |||
Relative Ranges in excel macro | Excel Discussion (Misc queries) | |||
Relative Ranges in excel macro | Excel Worksheet Functions | |||
Macro in Excel | Excel Discussion (Misc queries) |