String Formatting?

[Remalay]

I'm sure there is a relatively simple way of accomplishing this, but am unable to find it......


I am striving to find the method to format elements of a string, so that it retains those elements when written into a cell.

strText = "I want elements to be underlined, bold, or whatever, when pasted into a cell."

thanks.

[Charlize]

Maybe you can try this. The text is inserted when something changes in sheet1

[VBA]
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Sentence As String
Sentence = "bold and underline and italic and normal"
Worksheets(1).Range("A1").Select
ActiveCell.Value = Sentence
With Worksheets(1).Range("A1")
.Characters(1, 4).Font.Bold = True 'characters(start,end) bold on
.Characters(5, 40).Font.Bold = False 'bold off
.Characters(10, 18).Font.Underline = True
.Characters(19, 40).Font.Underline = False
.Characters(24, 29).Font.Italic = True
.Characters(30, 40).Font.Italic = False
End With
End Sub
[/VBA]
Charlize

[Remalay]

Thanks for the suggestion, Charlize, but your method will not suit my requirement. The ultimate string written into the cell is comprised from several smaller strings, where the order and content can be variable, as defined by the user. The 'start' and 'end' points of the formatting is therefore variable.

I'm probably wrong, but I thought there was a method of formatting the text 'in line', something like;
"I want elements to be &Uunderlined&U, &Bbold&B, or whatever ..."
to produce;
"I want elements to be underlined, bold, or whatever ..."

[Cyberdude]

You are thinking about the inline formatting available in this forum's post editor. Not sure, but I think that's HTML. I gotta admit, that's a nice feature, but it's not an Excel VBA feature.
Sid

[wnazzaro]

I've used a table to capture formatting from Word into an Outlook mail message, however I don't know if it would work for pasting into Excel. I use SendKeys to copy and paste, so it can cause problems, but perhaps that is a path to look down.

[geekgirlau]

The ultimate string written into the cell is comprised from several smaller strings, where the order and content can be variable, as defined by the user. The 'start' and 'end' points of the formatting is therefore variable.

Does this mean that all the text from one string should be italics, all the text from other string should be bold etc.?

[Remalay]

No. That would be too easy.

What I have is a variable listing of 3 to 5 bullet-point type headings followed by a few words of text. My objective is to emphasise the headings somehow.

[geekgirlau]

Where does the original text come from? MS Word?

[Charlize]

Maybe by using the length of the total string.
Then letter by letter adding to the cell.
Counting when a letter has been added (for start).
If & check for next value (B,U or I). What if you got bold, underline and italic ? add counting with two for start. End is before &
adding with 2 for next letter and keeping total length of string correct.
...

Charlize

[Remalay]

Charlize:
The use of the '&U', '&B', etc. was a potential method I incorrectly thought applied - these characters do not appear within the text, I was wrongly suggesting they might be put in to effect a formatting result.

Geekgirlau:
The text is all within excel.

In a drop-down cbx is a a listing of 10-12 pre-defined text strings. The user selects from the list, and these are placed, in the order chosen, into a worksheet as one text block, as part of a 'report' output.

Within a few of these pre-defined text strings are words and phrases that I would like to stand out within the final text display as sub-headings.

Given that the order is variable, the position if the required formatting is also variable.....

..... I suppose I could include obscure characters (eg ~, }, or #), specifically placed, within the predefined text strings, then 'find' their relative positions within the final text block (Instr), then reformat based upon those positions...??