formatting (underline/bold/strikethrough) in comment boxes

[adam l via OfficeKB.com]

Dear everybody
I need to copy and paste text into comment boxes that contain formating
(underlining/bold/strikethrough). however, when I copy it into the comment
box, the formatting is lost and I have to manually reformat the text which
will take me a million hours. Is there any way to copy text into the comment
box while retaining the formatting.
thanks very much
Adam l

 

[Dave Peterson]

Are you picking up the value from the same cell and putting it into the comment
(for the same cell)?

If yes, maybe you can do something like this that applies the formatting
character by character:

Option Explicit
Sub PutComment(FromRng As Range, ToRng As Range)

Dim iCtr As Long

Set FromRng = FromRng.Cells(1)
Set ToRng = ToRng.Cells(1)

If ToRng.Comment Is Nothing Then
'do nothing
Else
ToRng.Comment.Delete
End If

ToRng.AddComment Text:=FromRng.Value

For iCtr = 1 To Len(FromRng.Value)
With ToRng.Comment.Shape.TextFrame.Characters(Start:=iCtr, Length:=1)
.Font.Bold = FromRng.Characters(Start:=iCtr, Length:=1).Font.Bold
.Font.Underline _
= FromRng.Characters(Start:=iCtr, Length:=1).Font.Underline
.Font.Strikethrough _
= FromRng.Characters(Start:=iCtr, Length:=1).Font.Strikethrough
End With
Next iCtr
End Sub
Sub DoTheWork()

Dim myRng As Range
Dim myCell As Range

Set myRng = Worksheets("sheet1").Range("a1:A10")

For Each myCell In myRng.Cells
If myCell.Value <> "" Then
Call PutComment(myCell, myCell)
End If
Next myCell

End Sub

You could even use different cells if you want.

Change this line to what you need:

Call PutComment(myCell, myCell)
maybe to:
Call PutComment(myCell, myCell.offset(0,1))

 

[adam l via OfficeKB.com]

Dave
Having taken a bit of programming I sort of understand your suggestion. But,
I don't know how to use this type of language inside of excel. Is there a
short tutorial that would show me how to use your code, and to modify/create
my own when I need it for this specific problem or for others.
Thanks very much for your help
adam l

Are you picking up the value from the same cell and putting it into the comment
(for the same cell)?

If yes, maybe you can do something like this that applies the formatting
character by character:

Option Explicit
Sub PutComment(FromRng As Range, ToRng As Range)

Dim iCtr As Long

Set FromRng = FromRng.Cells(1)
Set ToRng = ToRng.Cells(1)

If ToRng.Comment Is Nothing Then
'do nothing
Else
ToRng.Comment.Delete
End If

ToRng.AddComment Text:=FromRng.Value

For iCtr = 1 To Len(FromRng.Value)
With ToRng.Comment.Shape.TextFrame.Characters(Start:=iCtr, Length:=1)
.Font.Bold = FromRng.Characters(Start:=iCtr, Length:=1).Font.Bold
.Font.Underline _
= FromRng.Characters(Start:=iCtr, Length:=1).Font.Underline
.Font.Strikethrough _
= FromRng.Characters(Start:=iCtr, Length:=1).Font.Strikethrough
End With
Next iCtr
End Sub
Sub DoTheWork()

Dim myRng As Range
Dim myCell As Range

Set myRng = Worksheets("sheet1").Range("a1:A10")

For Each myCell In myRng.Cells
If myCell.Value <> "" Then
Call PutComment(myCell, myCell)
End If
Next myCell

End Sub

You could even use different cells if you want.

Change this line to what you need:

Call PutComment(myCell, myCell)
maybe to:
Call PutComment(myCell, myCell.offset(0,1))

Dear everybody
I need to copy and paste text into comment boxes that contain formating

[quoted text clipped - 4 lines]

thanks very much
Adam l

 

[Dave Peterson]

You may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Dave
Having taken a bit of programming I sort of understand your suggestion. But,
I don't know how to use this type of language inside of excel. Is there a
short tutorial that would show me how to use your code, and to modify/create
my own when I need it for this specific problem or for others.
Thanks very much for your help
adam l

Are you picking up the value from the same cell and putting it into the comment
(for the same cell)?

If yes, maybe you can do something like this that applies the formatting
character by character:

Option Explicit
Sub PutComment(FromRng As Range, ToRng As Range)

Dim iCtr As Long

Set FromRng = FromRng.Cells(1)
Set ToRng = ToRng.Cells(1)

If ToRng.Comment Is Nothing Then
'do nothing
Else
ToRng.Comment.Delete
End If

ToRng.AddComment Text:=FromRng.Value

For iCtr = 1 To Len(FromRng.Value)
With ToRng.Comment.Shape.TextFrame.Characters(Start:=iCtr, Length:=1)
.Font.Bold = FromRng.Characters(Start:=iCtr, Length:=1).Font.Bold
.Font.Underline _
= FromRng.Characters(Start:=iCtr, Length:=1).Font.Underline
.Font.Strikethrough _
= FromRng.Characters(Start:=iCtr, Length:=1).Font.Strikethrough
End With
Next iCtr
End Sub
Sub DoTheWork()

Dim myRng As Range
Dim myCell As Range

Set myRng = Worksheets("sheet1").Range("a1:A10")

For Each myCell In myRng.Cells
If myCell.Value <> "" Then
Call PutComment(myCell, myCell)
End If
Next myCell

End Sub

You could even use different cells if you want.

Change this line to what you need:

Call PutComment(myCell, myCell)
maybe to:
Call PutComment(myCell, myCell.offset(0,1))

Dear everybody
I need to copy and paste text into comment boxes that contain formating

[quoted text clipped - 4 lines]

thanks very much
Adam l