Help getting a Macro to work!!

[HarryMonk]

Morning all,

I am writing a macro to update the titles of a workbook (including chart titles) of a budget spreadsheet in office 365, so that come the new month it updates the month in the various titles. I have it all working apart from the last chat title in the script (Dan_Chart). I'm fairly new to VBA so any suggestions on the current code that does works, please also feel free to comment.

The code I have so far is:

Sub Title_Update()
'
' Update Titles Macro
'
        Application.ScreenUpdating = False
        
        Worksheets("Monthly Report").Activate
        ActiveSheet.Unprotect
        
            Range("$B$1").Select
            ActiveCell.FormulaR1C1 = Format(Date, "mmmm") & " Joint Budget Report"
    
            Range("$V$1").Select
            ActiveCell.FormulaR1C1 = Format(Date, "mmmm") & " Joint (Budget)"
    
                ActiveSheet.ChartObjects("BudgetOverview").Activate
                ActiveChart.ChartTitle.Select
                ActiveChart.ChartTitle.Text = Format(Date, "mmmm") & " Expenses"
        
        ActiveSheet.Protect DrawingObjects:=False, AllowUsingPivotTables:=True
    
        Worksheets("Alex Budget").Activate
        ActiveSheet.Unprotect
    
            Range("$B$1").Select
            ActiveCell.FormulaR1C1 = Format(Date, "mmmm") & " Budget Report (Alex)"
    
                ActiveSheet.ChartObjects("Alex_Chart").Activate
                ActiveChart.ChartTitle.Select
                ActiveChart.ChartTitle.Text = Format(Date, "mmmm") & " Expenses"
            
        ActiveSheet.Protect DrawingObjects:=False, AllowUsingPivotTables:=True
    
        Worksheets("Dan Budget").Activate
        ActiveSheet.Unprotect
    
            Range("$B$1").Select
            ActiveCell.FormulaR1C1 = Format(Date, "mmmm") & " Budget Report (Dan)"
    
                ActiveSheet.ChartObjects("Dan_Chart").Activate
                ActiveChart.ChartTitle.Select
                ActiveChart.ChartTitle.Text = Format(Date, "mmmm") & " Expenses"
    
        ActiveSheet.Protect DrawingObjects:=False, AllowUsingPivotTables:=True
        
        Application.ScreenUpdating = True
        
End Sub

So its the last ActiveChart section for Dan_Chart. I do not receive any run time errors, just that section of code doesn't seem to update the chart title, which is strange because the same code works for the other two charts. I've looked at the various settings of the chart but they are all identical charts, just different data sets.

Any help or comments are appreciated!!​

[mdmackillop]

Can you post your workbook

[HarryMonk]

Think I've attached it right

[mdmackillop]

I can see the issue but not a solution. Running the code below shows that the property is changing, but not on the screen It is as though the title is overlaid by an unchanging text box.

Sub Test()
    Worksheets("Dan Budget").Activate
    ActiveSheet.Unprotect
    ActiveSheet.ChartObjects("Dan_Chart").Activate
    ActiveChart.ChartTitle.Select
    MsgBox ActiveChart.ChartTitle.Text
End Sub

[HarryMonk]

Just a bit weird.

Even deleted the pivot table and chart to see if it was bugged but didn't seem to fix it.

Have a work around where i have the months in a drop down which is linked to dynamic chart titles and then a formula in titles within cells which again are linked to the drop down box.

Thanks for taking a look

[rlv]

You have *two* ChartObjects, both named "Dan_Chart" on the same worksheet. One is empty and hard to see. I suspect if you delete that one, your macro will work.

[HarryMonk]

Spot on!!

Thanks rlv. Good to remember to check in future

[mdmackillop]

Hi RLV
I looked for that but couldn't see it. Well spotted.
MD

[rlv]

I wish I could claim some special observational brilliance, but the truth is that the only reason I saw it is because I've been bitten by this exact thing when working with chart objects in the past.