Code to insert textbox into chart

[rdw72777]

Hi all,

I have the code below and it runs, but sometimes it works and some times i get an error and I can't figure out why it only sometimes errors out.

The code errors out on the following line of code. The error is "Run time error -2147417848 Method "Add" of object "Textboxes" failed".

What's really weird is that this code will work on 2 charts and then error out on the first,m or it will not work on the first chart, but will on the second and third, and so on. Its simply inserting a textbox, and the behavior is inconsistent, so I'm at a loss. Wondering if its not dumping memory or something, as the behavior never seems to work the same way twice.

Line it errors on:

Set txtB = cht.TextBoxes.Add(chtT.Left, chtT.Top, 100, 50)

All code:

For n = 1 To myInitialEarlyStageCount - 1
        
             With DPUGraphSheet
             
                'Determine where to put to place sheet in file
                If n = 1 Then
                    .Select
                    .Copy After:=Sheets(ActiveSheet.Name)
                ElseIf n > 1 Then
                    .Select
                    .Copy After:=Sheets(myDPUName)
                End If
            
            End With
            
            'Name copied tab "myDPU" (temporary)
            ActiveSheet.Name = "myDPU"
            
            'Determine DPU Name, Rept unit name, next Rpt Unit Name
            With TAUDataSheet
                myDPU = .Cells(TAUDataESHeaderRow + n, TAUDataDisplayNameColumn).Value
                myRptUnit = .Cells(TAUDataESHeaderRow + n, TAUDataRptUnitColumn).Value
                myNextRptUnit = .Cells(TAUDataESHeaderRow + n + 1, TAUDataRptUnitColumn).Value
                'myFirstLSRptUnit
            End With
            
            PrjSummPrjIDColumnLetter = ColumnLetter(PrjSummPrjIDColumn)
            PrjSummPeriod1ColumnLetter = ColumnLetter(PrjSummPeriod1Column)
            PrjSummPeriod6ColumnLetter = ColumnLetter(PrjSummPeriod1Column + 5)
            
            'Rename new sheet
            myDPUName = Left(myDPU & " DPU by Prj", 30)
            Sheets("myDPU").Name = myDPUName
            
            'Determine Data ranges
            With PrjSummSheet.Columns(PrjSummPrjIDColumn)
                myRngStartRow = .Find(What:=myRptUnit, SearchDirection:=xlPrevious, LookAt:=xlWhole, SearchOrder:=xlByRows).Row + 1
                
                'For the last DPU to be graph
                myRngEndRow = .Find(What:=myNextRptUnit, SearchDirection:=xlPrevious, LookAt:=xlWhole, SearchOrder:=xlByRows).Row - 1
            End With
            
            'Determine range for last
            
            'Set interim ranges
            myRng1 = PrjSummPrjIDColumnLetter & PrjSummHeaderRow & ", "
            myRng2 = PrjSummPeriod1ColumnLetter & PrjSummHeaderRow & ":" & PrjSummPeriod6ColumnLetter & PrjSummHeaderRow & ", "
            myRng3 = PrjSummPrjIDColumnLetter & myRngStartRow & ":" & PrjSummPrjIDColumnLetter & myRngEndRow & ", "
            myRng4 = PrjSummPeriod1ColumnLetter & myRngStartRow & ":" & PrjSummPeriod6ColumnLetter & myRngEndRow
            myRngFinal = myRng1 & myRng2 & myRng3 & myRng4
            
            'Set data range for graph tab we've created
            Sheets(myDPUName).Select
            Sheets(myDPUName).SetSourceData Source:=Sheets("Prj Summary").Range(myRngFinal), PlotBy:=xlRows
                
            'Format/re-size legend and place it at top and centered
            Sheets(myDPUName).Legend.Left = 160.856
            Sheets(myDPUName).Legend.Top = 63.805
            Sheets(myDPUName).Legend.Width = 499.344
            Sheets(myDPUName).Legend.Left = 20.856
            Sheets(myDPUName).Legend.Width = 639.344
                
            'Rename chart title for DPU name and time period shown
            Set cht = ActiveChart

            ' Add a blank title
            cht.HasTitle = True
            Set chtT = cht.ChartTitle
            chtT.Text = vbLf
            
            'Set chart title variables
            ChartTitleLine1 = myDPU & " DPU"
            ChartTitleLine2 = "PTS Actual/Projected FTE Utilisation by Project"
            ChartTitleLine3 = Range("message18M").Value
            
            LenTitleLine1 = Len(ChartTitleLine1)
            LenTitleLine2 = Len(ChartTitleLine2)
            LenTitleLine3 = Len(ChartTitleLine3)
            
           
            
            ' Add and format textbox
            Set txtB = cht.TextBoxes.Add(chtT.Left, chtT.Top, 100, 50)
            txtB.HorizontalAlignment = xlCenter
            txtB.Font.Size = 18
            txtB.Text = ChartTitleLine1 & vbLf & ChartTitleLine2 & vbLf & ChartTitleLine3
            txtB.Characters(1, LenTitleLine1).Font.Bold = True
            txtB.Characters(LenTitleLine1 + 2, Len(ChartTitleLine2)).Font.Size = 14
            txtB.Characters(LenTitleLine1 + 2, Len(ChartTitleLine2)).Font.Bold = True
            txtB.Characters(LenTitleLine1 + 2, Len(ChartTitleLine2)).Font.Italic = True
            txtB.Characters(LenTitleLine1 + LenTitleLine2 + 3, Len(ChartTitleLine3)).Font.Size = 12
            txtB.Characters(LenTitleLine1 + LenTitleLine2 + 3, Len(ChartTitleLine3)).Font.Italic = True
            txtB.AutoSize = True
            
            txtB.Left = 167.856
            txtB.Top = 0
                       
        Next n