Multiple text formats within a single textbox

Question

I am having some trouble creating a textbox with multiple formats within the textbox. I would like to end result to look like this:

Item **1:**ABC | Item 2: DEF | Item 3 : GHI | Item 4: $#.##

A few things that make this difficult:

1. All text is being pulled from Excel 

a) Is there a way to format the text in Excel and have that carry over when I populate the textbox?

2. The number of words / characters for each item vary (for example, Item 1 can be 1 or 3 words, making it difficult to use the TextRange's                      Characters, Words, etc.

a) The output for each item, however, are grouped into about 5 different buckets. Is it possible to use multiple IF statements based on the contents in excel? For example;  If the value in the cell is = "specific value", then .TextFrame.TextRange.Words(1,2).Font.Bold=msoTrue , else, If the value is = "2nd Specific Value", then .TextFrame.TextRange.Words(1).Font.Bold=msoTrue, etc. 

3.  Item 4 needs to be in currency but is just being pulled in as 50 rather than $50.00

Here is the current code I am using to add the  text:

' Create textbox with additional information

Dim tbAdditional As Shape

Set tbAdditional = Sld.Shapes("Text Placeholder 10")

With tbAdditional

    .TextFrame.TextRange.ParagraphFormat.Alignment = ppAlignLeft

    .TextFrame.TextRange.Font.Color.RGB = RGB(90, 90, 90)

    .TextEffect.FontName = "Arial (Body)"

    .TextEffect.FontSize = 12

    .TextFrame.TextRange.Text = oWb.Worksheets(1).Range("E1").Value & " " & oWb.Worksheets(1).Cells(i + 1, "E").Value & " | " & _

        oWb.Worksheets(1).Range("F1").Value & " " & oWb.Worksheets(1).Cells(i + 1, "F") & " | " & _

        oWb.Worksheets(1).Range("G1").Value & " " & oWb.Worksheets(1).Cells(i + 1, "G") & "| " & _

        oWb.Worksheets(1).Range("H1").Value & " " & oWb.Worksheets(1).Cells(i + 1, "H") 

Thanks

Answer 1

>> 2. The number of words / characters for each item vary (for example, Item 1 can be 1 or 3 words, making it difficult to use the TextRange's Characters, Words, etc.

Is it fair to assume, though, that the part in bold ("Item 1:" etc) will always be as shown and that there will always be four items.

>> a) The output for each item, however, are grouped into about 5 different buckets. Is it possible to use multiple IF statements based on the contents in excel? For example;  If the value in the cell is = "specific value", then .TextFrame.TextRange.Words(1,2).Font.Bold=msoTrue , else, If the value is = "2nd Specific Value", then .TextFrame.TextRange.Words(1).Font.Bold=msoTrue, etc. 

Multiple indented IF statements are possible, but Case Selectors can be easier to write correctly and to maintain later:

Select Case {the cell value}

  Case Is = "specific value"

     ' do the right thing

  Case Is = "2nd specific value"

   ' do the second right thing

'  And so on; it's always a good idea to include an Else case

  Case Else

     MsgBox "Run around in circles yelling THE SKY IS FALLING"

End Select

>> 3.  Item 4 needs to be in currency but is just being pulled in as 50 rather than $50.00

Use this:

Format(Value,"$####.00")

Answer 2

Hi Steve,

Thanks for taking the time to help out. I was able to successfully complete the macro by using cases and by using the Format (value, "$####.00").

Thank you so much for your help!