3 lines in a cell with each line having different fontsize..

H

hollifd

Board Regular
Joined
Apr 3, 2002
Messages
248
Using VBA, how would I write the code to insert multiple lines of text into a cell and specify the fontsize of each line...

Code: 
Item1$ = "FontSize 18"  'I want this to display on the worksheet in FontSize 18
Item2$ = "FontSize 20"  'I want this to display on the worksheet in FontSize 20
Item3$ = "FountSize 22"  'I want this to display on the worksheet in FontSize 22

Range("DifferentFonts").Value = Item1$ & Chr$(10) & Item2$ & Chr$(10) & Item3$ & Chr$(10)

Thanks for any help,

David
 
Sort by date Sort by votes
You'll have to find the starting point and the number of characters for each item and format them separately (just as if you were manually selecting the text in the cell and formatting it).

This seems to be working for me:
Code: 
Sub test()
Dim Item1 As String, Item2 As String
Dim Item3 As String, FullText As String
Dim NumChar1 As Integer, NumChar2 As Integer, NumChar3 As Integer
Dim Start1 As Integer, Start2 As Integer, Start3 As Integer

'I want this to display on the worksheet in FontSize 18
Item1 = "FontSize 18" 'text
NumChar1 = Len(Item1) '# of characters
Start1 = 1 'where first character is located in string

'I want this to display on the worksheet in FontSize 20
Item2 = "FontSize 20" 'text
NumChar2 = Len(Item2) '# of characters
Start2 = NumChar1 + 2 'where first character is located in string

'I want this to display on the worksheet in FontSize 22
Item3 = "FontSize 22" 'text
NumChar3 = Len(Item3) '# of characters
Start3 = NumChar1 + NumChar2 + 3 'where first character is located in string

'full text string
FullText = Item1 & Chr(10) & Item2 & Chr(10) & Item3


With Range("DifferentFonts")
    'insert text string into cell
    .Value = FullText
    'format font sizes of text
    .Characters(Start1, NumChar1).Font.Size = 18
    .Characters(Start2, NumChar2).Font.Size = 20
    .Characters(Start3, NumChar3).Font.Size = 22
End With

End Sub
 
Upvote 0
Von Pookie,

I have another related problem that I need help with. When I add text to a cell that already has text in it, the formatting for the original text gets removed. How can I preserve the original text's formatting and still format the newly added text to a cell? Can you think of an easy solution?

Thanks,

David
 
Upvote 0
David

How are you adding to the cell?
 
Upvote 0
Norie,

I am using .value = .value & Item1$ & chr$(10) & Item2$ & chr$(10) etc.


Does that help?
 
Upvote 0
I just ran a quick test with text in the cell that was formatted as 14 point and italics. When I ran the code, it kept the italics for all of the text, but the font sizes were all messed up. The font size issue, however, simply comes down to where the text is in the cell.

If there is already text in the cell, you can't use "1" to note the placement of the Item1 text, since that means the first character in the cell.

What are you wanting to do, exactly? Put the 3 lines of code under whatever text is currently in the cell?
 
Upvote 0
Assuming my above assumption is correct and you want to put the font size lines under whatever text is in the cell, this seems to work:

Code: 
Sub test()
Dim currVal As String, Item1 As String, Item2 As String
Dim Item3 As String, FullText As String
Dim CurrChar As Integer, NumChar1 As Integer, NumChar2 As Integer, NumChar3 As Integer
Dim Start1 As Integer, Start2 As Integer, Start3 As Integer

'current cell value
currVal = Range("DifferentFonts").Value 'text
CurrChar = Len(currVal) '# of characters

'I want this to display on the worksheet in FontSize 18
Item1 = "FontSize 18" 'text
NumChar1 = Len(Item1) '# of characters
Start1 = CurrChar + 2 'where first character is located in string

'I want this to display on the worksheet in FontSize 20
Item2 = "FontSize 20" 'text
NumChar2 = Len(Item2) '# of characters
Start2 = CurrChar + NumChar1 + 3 'where first character is located in string

'I want this to display on the worksheet in FontSize 22
Item3 = "FontSize 22" 'text
NumChar3 = Len(Item3) '# of characters
Start3 = CurrChar + NumChar1 + NumChar2 + 4 'where first character is located in string

'full text string
FullText = currVal & Chr(10) & Item1 & Chr(10) & Item2 & Chr(10) & Item3


With Range("DifferentFonts")
    'insert text string into cell after current value
    .Value = FullText
    'format font sizes of text
    .Characters(Start1, NumChar1).Font.Size = 18
    .Characters(Start2, NumChar2).Font.Size = 20
    .Characters(Start3, NumChar3).Font.Size = 22
End With

End Sub

Just have to recalculate where the first character of the strings are in the cell.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
Clean Text - Remove Repeat Line Breaks
Replies
4
Views
820
lneidorf
L
O
  • Question Question
Cannot trap error when using Excel's built-in stock price functionality in a macro. It used to work well!
Replies
1
Views
326
OaklandJim
O
R
  • Question Question
Required Transposed Values instead of Row Items of Duplicate Invoice no with Each GSTIN which is displaying the SUM of 12+18+28
Replies
1
Views
127
rk_rk
R
S
  • Solved
Web Query in VBA Loop through tables on page to choose get tags from table instead of text
2
Replies
13
Views
807
Domenic
D
TAPS_MikeDion
  • Solved
Use VBA to add year to header for all sheets
Replies
5
Views
381
Joe4
Joe4

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top