Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Aligning comments
My macro gets comments from a legacy system and builds a varaible called
"notes". The comments consist of date, time, user ID and freeform text. I build a string with spaces between the different fields to try and line them up because there can be 5, 10, or more comments retrieved for each item. Once all of the comments have been retrieved from the legacy system, the "notes" variable is pasted in a cell as an Excel comment. This part works well; however, when the comments are displayed it looks really ragged. I would like to try and line up the fields a little better like this: 1/15/07 2:15 PM 12345 Joe said it is okay to pay 10/07/06 3:45 AM 124AB Check should arrive any day I once used "Courier New" as the text style in a combo box to line up different fields and it worked really well. I am using the following code to go back and format all of the comments on a particular sheet once all of the comments have been pasted in Excel. However, they still aren't lining up like I want them to. Sub MyComments() Dim w As Worksheet Dim ct As Comment For Each w In ActiveWorkbook.Worksheets If w.Name = "My List" Then For Each cmt In ws.Comments With ct.Shape.TextFrame.Characters.Font .Name = "Courier New" .Size = 9 End With Next ct End If Next w End Sub Is it even possible to do what I want? If so any suggestions would be greatly appreciated. Thanks for the help.........JT -- JT |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Aligning comments
given the text contains the appropriate number of spaces to line them up and
the comment box is wide enough so you don't get word wrap, using a monospaced font like courier new should work. -- Regards, Tom Ogilvy "JT" wrote: My macro gets comments from a legacy system and builds a varaible called "notes". The comments consist of date, time, user ID and freeform text. I build a string with spaces between the different fields to try and line them up because there can be 5, 10, or more comments retrieved for each item. Once all of the comments have been retrieved from the legacy system, the "notes" variable is pasted in a cell as an Excel comment. This part works well; however, when the comments are displayed it looks really ragged. I would like to try and line up the fields a little better like this: 1/15/07 2:15 PM 12345 Joe said it is okay to pay 10/07/06 3:45 AM 124AB Check should arrive any day I once used "Courier New" as the text style in a combo box to line up different fields and it worked really well. I am using the following code to go back and format all of the comments on a particular sheet once all of the comments have been pasted in Excel. However, they still aren't lining up like I want them to. Sub MyComments() Dim w As Worksheet Dim ct As Comment For Each w In ActiveWorkbook.Worksheets If w.Name = "My List" Then For Each cmt In ws.Comments With ct.Shape.TextFrame.Characters.Font .Name = "Courier New" .Size = 9 End With Next ct End If Next w End Sub Is it even possible to do what I want? If so any suggestions would be greatly appreciated. Thanks for the help.........JT -- JT |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Aligning comments
Format the date/time to ensure the same number of characters:
?format("1/15/07","mm/dd/yy hh:mm AM/PM") And assuming the userID is a constant length, the fixed-width font should line up. You would still need to ensure the text frame is wide enough to display all characters. You can get the display width from Len(Longest Line) * Scale Factor, which you can get with a little trial and error. NickHK "JT" ... My macro gets comments from a legacy system and builds a varaible called "notes". The comments consist of date, time, user ID and freeform text. I build a string with spaces between the different fields to try and line them up because there can be 5, 10, or more comments retrieved for each item. Once all of the comments have been retrieved from the legacy system, the "notes" variable is pasted in a cell as an Excel comment. This part works well; however, when the comments are displayed it looks really ragged. I would like to try and line up the fields a little better like this: 1/15/07 2:15 PM 12345 Joe said it is okay to pay 10/07/06 3:45 AM 124AB Check should arrive any day I once used "Courier New" as the text style in a combo box to line up different fields and it worked really well. I am using the following code to go back and format all of the comments on a particular sheet once all of the comments have been pasted in Excel. However, they still aren't lining up like I want them to. Sub MyComments() Dim w As Worksheet Dim ct As Comment For Each w In ActiveWorkbook.Worksheets If w.Name = "My List" Then For Each cmt In ws.Comments With ct.Shape.TextFrame.Characters.Font .Name = "Courier New" .Size = 9 End With Next ct End If Next w End Sub Is it even possible to do what I want? If so any suggestions would be greatly appreciated. Thanks for the help.........JT -- JT |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Aligning comments
I haven't followed the thread, only this -
You would still need to ensure the text frame is wide enough to display all characters. Dim cm As Comment, s$ Set cm = ActiveSheet.Comments(1) s = "This is line 1" & vbLf & _ "Line 2 is a much longer line" & vbLf & _ "Line 3" cm.Text Text:=s cm.Shape.TextFrame.AutoSize = True msgbox cm.Shape.Width If all comments should be same width when done, trap the largest width, re-set each AutoSize = false, and size all to largest. Regards, Peter T "NickHK" wrote in message ... Format the date/time to ensure the same number of characters: ?format("1/15/07","mm/dd/yy hh:mm AM/PM") And assuming the userID is a constant length, the fixed-width font should line up. You would still need to ensure the text frame is wide enough to display all characters. You can get the display width from Len(Longest Line) * Scale Factor, which you can get with a little trial and error. NickHK "JT" ... My macro gets comments from a legacy system and builds a varaible called "notes". The comments consist of date, time, user ID and freeform text. I build a string with spaces between the different fields to try and line them up because there can be 5, 10, or more comments retrieved for each item. Once all of the comments have been retrieved from the legacy system, the "notes" variable is pasted in a cell as an Excel comment. This part works well; however, when the comments are displayed it looks really ragged. I would like to try and line up the fields a little better like this: 1/15/07 2:15 PM 12345 Joe said it is okay to pay 10/07/06 3:45 AM 124AB Check should arrive any day I once used "Courier New" as the text style in a combo box to line up different fields and it worked really well. I am using the following code to go back and format all of the comments on a particular sheet once all of the comments have been pasted in Excel. However, they still aren't lining up like I want them to. Sub MyComments() Dim w As Worksheet Dim ct As Comment For Each w In ActiveWorkbook.Worksheets If w.Name = "My List" Then For Each cmt In ws.Comments With ct.Shape.TextFrame.Characters.Font .Name = "Courier New" .Size = 9 End With Next ct End If Next w End Sub Is it even possible to do what I want? If so any suggestions would be greatly appreciated. Thanks for the help.........JT -- JT |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel 2000 how to format the comments font all comments | Excel Discussion (Misc queries) | |||
Aligning rows. Help... | Excel Discussion (Misc queries) | |||
in excel useing comments how do you add clip art to comments? | New Users to Excel | |||
Aligning decimal numers to the centre of the cell and aligning dec | Excel Discussion (Misc queries) | |||
Aligning text | Excel Discussion (Misc queries) |