PDA

View Full Version : [SOLVED] Help getting a Macro to work!!



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!!​

mdmackillop
03-12-2017, 04:10 AM
Can you post your workbook (http://www.vbaexpress.com/forum/faq.php?s=&do=search&q=attach&titleandtext=1&match=all)

HarryMonk
03-12-2017, 04:21 AM
Think I've attached it right : pray2:: pray2:

mdmackillop
03-12-2017, 12:03 PM
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
03-12-2017, 12:39 PM
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 :hi:

rlv
03-12-2017, 01:39 PM
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
03-12-2017, 02:53 PM
Spot on!!

Thanks rlv. Good to remember to check in future :rofl:

mdmackillop
03-12-2017, 03:07 PM
Hi RLV
I looked for that but couldn't see it. Well spotted.:clap2:
MD

rlv
03-12-2017, 03:50 PM
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.