Code to format comment box text

[Slick9]

Good morning all,

I have a program that I'm working on and it was going well till I hit the wall. I need to paste cell content as comments in about 1000 cells (all in the same column). I've figured out how to paste the cell content into the comment boxes. The problem is I need all the comment boxes to be formatted in a particular way. I need to have the comments formatted into two columns, with 8 spaces after the colon (if there was a way to left justify both columns so i wouldn't need to put a specific number of spaces in between them that would be a huge bonus), and the words in the second column bolded. Here's a sample of what the info going into the comment boxes will look like.

Aircraft: Boeing
Model: 777-200
Engine: GE-90B
Maint Cycle: 4X1BB
Base: KDEN

The challenge is that I need spaces between the label (Aircraft) and the variant (Boeing) and so on down the list, I also need all the variants (everything after the colon to be bolded).

I found the code below with google and it comes close to doing what I need, with a few differences, the code below formats the information in the cell itself, not the info in the comment box. The other problem is that this code formats the letters before the colon and not after the colon like I need. I would really appreciate it if someone could modify this code for me, it would be a HUGE help!!!!!

Thanks for any help;






Sub FormatActiveCells()
Dim c As Range
For Each c In Selection.Cells
FormatCell c
Next
End Sub

' This subroutine does the work
Sub FormatCell(c As Range)
Dim pos1 As Integer, pos2 As Integer
' Determine if line 1 is Symbol
pos1 = InStr(1, c.Text, "Symbol:")
If pos1 > 0 Then
' Make "Symbol:" bold
c.Characters(pos1, Len("Symbol:")).Font.FontStyle = "Bold"
' Advance past colon character
pos1 = pos1 + Len("Symbol:")
' Find end-of-line character
pos2 = InStr(pos1, c.Text, Chr(10))
' Make text between "Symbol:" and end-line italicized and colored
c.Characters(pos1, pos2 - pos1).Font.FontStyle = "Bold Italic"
c.Characters(pos1, pos2 - pos1).Font.Color = RGB(0, 0, 255)
' Point both positions to one character past end-of-line
pos2 = pos2 + 1
pos1 = pos2
Else
' Point both positions to first character
pos1 = 1
pos2 = 1
End If

' Format additional lines
Do
' Find colon character
pos2 = InStr(pos1, c.Text, ":")
' If not found, we're done
If pos2 = 0 Then Exit Do
' Make text from start of line to colon bold
c.Characters(Start:=pos1, Length:=pos2 - pos1).Font.FontStyle = "Bold"
' Find end-of-line
pos2 = InStr(pos2 + 1, c.Text, Chr(10))
' If not found, we're done
If pos2 = 0 Then Exit Do
' Point both positions to one character past end-of-line
pos2 = pos2 + 1
pos1 = pos2
DoEvents
Loop
End Sub

Sub FormatActiveCells_1()
Dim cmt As Comment
For Each Comment In Selection.Cell
FormatCell cmt
Next
End Sub

' This subroutine does the work
Sub FormatComment(c As Range)
Dim pos1 As Integer, pos2 As Integer
' Determine if line 1 is Symbol
pos1 = InStr(1, c.Text, "Symbol:")
If pos1 > 0 Then
' Make "Symbol:" bold
c.Characters(pos1, Len("Symbol:")).Font.FontStyle = "Bold"
' Advance past colon character
pos1 = pos1 + Len("Symbol:")
' Find end-of-line character
pos2 = InStr(pos1, c.Text, Chr(10))
' Make text between "Symbol:" and end-line italicized and colored
c.Characters(pos1, pos2 - pos1).Font.FontStyle = "Bold Italic"
c.Characters(pos1, pos2 - pos1).Font.Color = RGB(0, 0, 255)
' Point both positions to one character past end-of-line
pos2 = pos2 + 1
pos1 = pos2
Else
' Point both positions to first character
pos1 = 1
pos2 = 1
End If

' Format additional lines
Do
' Find colon character
pos2 = InStr(pos1, c.Text, ":")
' If not found, we're done
If pos2 = 0 Then Exit Do
' Make text from start of line to colon bold
c.Characters(Start:=pos1, Length:=pos2 - pos1).Font.FontStyle = "Bold"
' Find end-of-line
pos2 = InStr(pos2 + 1, c.Text, Chr(10))
' If not found, we're done
If pos2 = 0 Then Exit Do
' Point both positions to one character past end-of-line
pos2 = pos2 + 1
pos1 = pos2
DoEvents
Loop
End Sub

[Kevin#]

Just noticed that nobody has replied to you on this.

In which column(s) in which sheet is the text currently held
Into which column in which sheet are the comments to be added?

thanks

[Kevin#]

Rather than try to tailor your code, this is a fresh start. This can be modified to suit your need. Ask if you want any help doing that. Also if there is anything in the formatting that is not quite as you want, then easy to amend.

What the macro does
The comments enclosed within in the macro should make it all fairly self-explanatory
The macro takes the values in columnA, splits the text into 2 components and puts the values in columns B & C, then formats those values as required, before combining them as cell comments in columnF

To run the macro in the attached workbook {ctrl} + t
There is also a macro to clear back to the original values {ctrl} + {shift} + t

Please Login or Register  to view this content.

[Kevin#]

For some reason it is not letting me attach a file at the moment - webpage crashes every time!
I will try again later

[Kevin#]

Trying to upload file again

[Kevin#]

Paperclip does nothing and ManageAttachments appears to do everything correctly but nothing appears!!
Yet another attempt

[Kevin#]

This is crazy - there must be some big delay in the system - I checked and there were no files on any of the posts and now they are all there!!
It's like waiting for a bus for ages and then 3 come along all at the same time!!

[Slick9]

This is crazy - there must be some big delay in the system - I checked and there were no files on any of the posts and now they are all there!!
It's like waiting for a bus for ages and then 3 come along all at the same time!!

Thanks Kevin,

I'll run this code today and let you know how it works, much appreciated!!!!!

[Slick9]

Hi Kevin - I had a chance to run the macro - it's doing what I need with one exception, it's putting these lines in as separate comments in separate cells. All the data belong to one aircraft so I actually need the user to click on a cell with a "tail number" in it and in the comment box for that cell all this data will be displayed together. Each cell will have 5 lines in it's comment box. So I need the comment box to look like this:

Aircraft: Boeing
Model: 777-200
Engine: GE-90B
Maint Cycle: 4X1BB
Base: KDEN

So the way your code is parsing the data and creating two columns before converting into a comment is perfect, i just need all this to be in one comment box. The idea being the user clicks on the cell and in the comment box for that cell all the relevant data for that particular aircraft is available.

I really appreciate your help. Thank you very much!!!

Richard

[Kevin#]

Are you wanting to modify each comment individually? Code could be modified so that multiple aircraft details put in column A (in 6 row groupings) are converted to comments at one hit

Code modified as follows:
- cTxt variable added to combine the comment text into a single string, which is now added to cell E2 as a comment
- added spaces to the first element to make it a consistent length
- changed comment box font to "Courier New" - this is a monospaced font (all characters = same width) and therefore all the characters will line up prettily in the comment box
- autosized comment box

Please Login or Register  to view this content.

[Slick9]

Thanks a million Kevin!! Been on the road and just got back last night, so I'll try this out today, Again, truly appreciate the help.

Richard