With Selection.Format.TextFrame2. Run Error

[eaddi]

Hello,

I am work with a spreadsheet and I am having difficulties with this code. I keep getting a runtime error at the .ParagraphFormat and .Font lines. I did not write this code, bu tI need help in understanding whats wrong.


ActiveSheet.ChartObjects("Chart 14").Activate
ActiveChart.ChartTitle.Select
ActiveChart.ChartTitle.Text = MonthName(Month(CDate(Application.WorksheetFunction.EoMonth(Date, Plain(48))))) _
& " " & Year(Application.WorksheetFunction.EoMonth(Date, Plain(48))) _
& " FHLB Advance Composition"
Selection.Format.TextFrame2.TextRange.Characters.Text = _
MonthName(Month(CDate(Application.WorksheetFunction.EoMonth(Date, Plain(48))))) _
& " " & Year(Application.WorksheetFunction.EoMonth(Date, 0)) _
& " FHLB Advance Composition"
With Selection.Format.TextFrame2.TextRange.Characters(1, 43).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 40).Font
.BaselineOffset = 0
.Bold = msoTrue
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 13.8
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Strike = msoNoStrike
End With

[SamT]

The last selected object is
ActiveSheet.ChartObjects("Chart 14").ChartTitle.Select

ChartTitles don't have a Format Method. This code
Selection.Format.TextFrame2.TextRange.Characters.Text
is meaningless in this context.

TextFrame2 is a property of an Excel 2007 ChartFormat object. See http://msdn.microsoft.com/en-us/library/office/ff840633.aspx

I don't have XL 2007, Sorry

[eaddi]

This code was created last Oct by another code writer. The workbook has been working great up until March. The spread sheet is doing weird stuff. Sometimes the complete code works and sometimes I get a run error. I tried to debug the code by stepping into it. The code I posted was the first run time error and the second was the text. Tomorrow I will share the complete code, maybe the complete code will help. The whole code updates all dates in the spreadsheet, insert new columns in various sheets for the new month, and update the charts.

As a side note how did you get started with coding?

[SamT]

eaddi,


About the VBA Code Cleaner

During the process of creating VBA programs a lot of junk code builds up in your files. If you don't clean your files periodically you will begin to experience strange problems caused by this extra baggage. Cleaning a project involves exporting the contents of all its VBComponents to text files, deleting the components and then importing the components back from the text files.
http://www.appspro.com/Utilities/CodeCleaner.htm


On a side Note :beerchug:

I was laid up with cancer and needed a hobby to stay sane. Well, the laid up part was permanent and the hobby became a life's calling.

[Aflatoon]

Untested, but maybe:
With ActiveSheet.ChartObjects("Chart 14").Chart.ChartTitle
.Text = MonthName(Month(CDate(Application.WorksheetFunction.EoMonth(Date, Plain(48))))) _
& " " & Year(Application.WorksheetFunction.EoMonth(Date, 0)) _
& " FHLB Advance Composition"
With .Format.TextFrame2.TextRange

With .Characters.ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With

With .Characters(1, 40).Font
.BaselineOffset = 0
.Bold = msoTrue
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
With .Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(0, 0, 0)
.Transparency = 0
.Solid
End With
.Size = 13.8
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Strike = msoNoStrike
End With

End With

End With

[eaddi]

Aflatoon- your code did work (so now it has been tested).
SamT- I will do a clean the project this afternoon. I do think the worksheet needs it.

Thank you both

[eaddi]

eaddi,


On a side Note :beerchug:

I was laid up with cancer and needed a hobby to stay sane. Well, the laid up part was permanent and the hobby became a life's calling.


Sam I would ike to discuss how you were able to turn your life calling into a profitable career. i would aprreciate any tips, pointers, or suggestion you can offer. Sorry fror this relpy, but I cannot PM yet.