Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
formatting (underline/bold/strikethrough) in comment boxes
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:=iC tr, 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 |
#3
|
|||
|
|||
formatting (underline/bold/strikethrough) in comment boxes
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:=iC tr, 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 |
#4
|
|||
|
|||
formatting (underline/bold/strikethrough) in comment boxes
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:=iC tr, 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comment Boxes | Excel Discussion (Misc queries) | |||
adding scroll bar to comment boxes | Excel Discussion (Misc queries) | |||
Code to delete all comment boxes | Excel Discussion (Misc queries) | |||
Cell comment boxes shouldn't change size/shape unless changed by u | Excel Discussion (Misc queries) | |||
Comment Boxes | Excel Worksheet Functions |