HarryMonk
03-12-2017, 01:04 AM
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!!
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!!