formatting (underline/bold/strikethrough) in comment boxes

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

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" wrote:
>
> 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

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



Dave Peterson wrote:
>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
>


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200511/1

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



"adam l via OfficeKB.com" wrote:
>
> 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
>
> Dave Peterson wrote:
> >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
> >
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...excel/200511/1

--

Dave Peterson