Fonts and Concatenate

Dave Harbottle · #1 07-29-2009, 04:11 AM

Hi, I'm trying to use concatenate to create a title of a graph as that the total variance month by month without having to manually update.

However, we're wanting the figures in a smaller font and italic but it seems its an all or nothing font setting.

Is it possible to have part of the concatenate a different font etc and if so, how?
D

DonkeyOte · #2 07-29-2009, 04:24 AM

So are you saying you're using a Formula to generate the physical Chart Title or are you putting the description into a standard cell ?
Basically speaking it is not possible to use a Formula in a cell and apply different formats to different sections of the cell, in order to do that the cell entry needs essentially to be "value only" (ie no formula)... in general for the value to then be dynamic you would need to use VBA... if you're referring to the Chart Title itself I have even less knowledge as to whether or not you can even use VBA to apply different formats to specific elements of the Title itself... let us know exactly what you're doing and what you're concatenating etc... ie which cells, when/how they alter etc...

Dave Harbottle · #3 07-29-2009, 05:18 AM

I'm linking the title to a particular cell which is where the concatenate formula is:
=CONCATENATE(I73,CHAR(13),"Total Variance ","£",ROUND(O38/1000,0),"k"," ","Percentage"," ",O41,"%")

The consensus we had here was it may not be possible but I just dont want to use text boxes if I can avoid it!

DonkeyOte · #4 07-29-2009, 08:26 AM

Apologies for delay...

I was doing some testing and for whatever reason I'm unable to apply multiple formats to the Chart Title characters via VBA... I've moved this to the Charts Forum - hopefully Andy (Pope) will see this and respond accordingly.

Dave Harbottle · #5 07-29-2009, 08:50 AM

Thank you!

Andy Pope · #6 07-29-2009, 08:51 AM

You can not format parts of the result of a formula.

You will need to use VBA to set the text and format the required parts.
In the attached the formula is in C2 with the 3 components in A1:A3

The following located and formats the 2nd and 3rd components.

Code:

Sub ChartTitle()

    Dim strText As String
    Dim lngStart As Long
    Dim lngEnd As Long
    
    With ActiveSheet.ChartObjects(1).Chart
        .HasTitle = True
        .ChartTitle.Text = Range("C2").Value
        .ChartTitle.Font.Size = 20
        
        strText = .ChartTitle.Text
        lngStart = InStr(strText, "Total Variance") + Len("Total Variance") + 1
        lngEnd = InStr(strText, "Percentage")
        .ChartTitle.Characters(lngStart, lngEnd - lngStart).Font.Size = 8
        
        lngStart = lngEnd + Len("Percentage") + 1
        lngEnd = Len(strText)
        .ChartTitle.Characters(lngStart, lngEnd - lngStart + 1).Font.Size = 8
        
    End With
End Sub

DonkeyOte · #7 07-29-2009, 08:56 AM

Hi Andy, yes, this approach doesn't work for me in XL2007 ... ie the tweaked format is applied to the entire title as opposed to just the sub-selection of characters in the string.... is it a version specific difference or am I being a dunce ?

Andy Pope · #8 07-29-2009, 09:00 AM

Will investigate further for '07 solution.

Andy Pope · #9 07-29-2009, 11:49 AM

Looks like a bug in xl2007. The formatting is applied to all text in the specified range.

So you will need a embedded textbox and then this code.

Code:

Sub ChartTitle()

    Dim strText As String
    Dim lngStart As Long
    Dim lngEnd As Long
    Dim objTF2 As TextFrame2
    Dim shpTemp As Shape
    
    Set shpTemp = ActiveSheet.ChartObjects(1).Chart.Shapes(1)
    With shpTemp
        Set objTF2 = .TextFrame2
        With objTF2.TextRange
            .Font.Size = 20
            strText = Range("C2").Value
            .Characters.Text = strText
            
            lngStart = InStr(strText, "Total Variance") + Len("Total Variance") + 1
            lngEnd = InStr(strText, "Percentage")
            .Characters(lngStart, lngEnd - lngStart).Font.Size = 8
            
            lngStart = lngEnd + Len("Percentage") + 1
            lngEnd = Len(strText)
            .Characters(lngStart, lngEnd - lngStart + 1).Font.Size = 8
            
        End With
    End With

End Sub

DonkeyOte · #10 07-29-2009, 11:53 AM

Andy, thanks for that, reassured to know I wasn't being a "muppet"... this time

Jbentley · #11 07-30-2009, 06:40 AM

If the chart is embedded in a worksheet you could always try a less elegant approach...

Set the Chart Area to 'No Fill' and format the cells behind however you like...

Adjust column widths and have a cell for each differently formatted data in the 'Chart Title'.

As I said, not elegant, but possibly easier for the non-VBA Excel user.

Welcome to the Excel Forum

Forum Statistics