VBA - Change font size during string

Posted on 2012-09-09
Last Modified: 2012-09-24
Hi Guys,

I have a chart title which I neet to change the font size for the 2nd line in the title so the first line appears in a large font and the 2nd line which will be a date appears in a smaller font. I have the code below which puts the correct text on both lines but I'm unsure how to change the font size for the 2nd line. Any ideas would be very appreaciated

ActiveChart.ChartTitle.Text = "Quality Gates - September" & Chr(10) & "From: " & Format(now(),"dd/mm/yyy")


Question by:victoriaharry
    LVL 44

    Expert Comment

    by:Martin Liss
    You can't have more than one font size, color etc in a textbox. If Excel has a richtextbox you can use that but you should probably consider using two textboxes and splitting the text between the two.

    Author Comment

    Thanks for the reply. I'm al little confused though. If I create a chart manually with two lines of text in the title I can highlight the 2nd line of text and change the font size to whatever I want so that the size differs from the first line. I was hoping there was a way to replicate this action using VBA
    LVL 44

    Expert Comment

    by:Martin Liss
    Try creating and then editing a macro while you are achieving the effect that you want.
    LVL 92

    Accepted Solution

    This works in Excel 2010.  Haven't tested it in other versions:

    Dim TitleText As String
    Dim BreakPoint As Long
    TitleText = "Quality Gates - September" & Chr(10) & "From: " & _
        Format(Now, "dd/mm/yyyy")
    BreakPoint = InStr(1, TitleText, Char(10))
    With ActiveChart.ChartTitle
        .Text = "Quality Gates - September" & Chr(10) & "From: " & _
        .Characters(1, BreakPoint - 1).Font.Size = 18
        .Characters(TitleText + 1, Len(TitleText)).Font.Size = 10
    End With

    Open in new window


    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Suggested Solutions

    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now