PDA

View Full Version : [SOLVED] Chart title not updating



Nmarkit
08-29-2013, 08:41 AM
Hello

I have 3 radio buttons - revenue, volume1 and volume2 - and one combo box for client name. The user selects a client from the filter and then selects one of the 3 radio buttons to view the corresponding graph. The graphs are based on 3 different pivot tables on 3 separate worksheets. the entire setup is working fine using macros/vba. however i am struggling with the chart titles. When the user selects a client name A and then selects say revenue then he sees teh relevant graph and correct chart title. however if he now selects say client B from drop down leaving revenue selected then the data on the chart gets updated for client B however the title still says client A. the user has to click on one of the other radio buttons to refresh the title. is there any way i can have the title refresh at the time of the client selection no matter what radio button is clicked on?

I hope the above makes sense, please let me know if any more info is needed

thanks!

p45cal
08-29-2013, 01:59 PM
We'd need to see the existing code.

Nmarkit
08-30-2013, 01:03 AM
Thanks for your response p45cal. here;s the bit that creates the charts...


Private Sub OptionButton2_Click()
'revenue trends
On Error Resume Next
ActiveSheet.ChartObjects.Delete


Dim objCht As ChartObject
On Error Resume Next
Set objCht = ActiveSheet.ChartObjects("Challenges")
If Not objCht Is Nothing Then
objCht.Delete
End If

ActiveSheet.Shapes.AddChart.Select
ActiveChart.Parent.Name = "Challenges"
ActiveChart.HasLegend = False
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Text = Worksheets("Dashboard").Range("E9")
ActiveChart.ChartTitle.Font.Size = 12
ActiveChart.ChartTitle.Font.Name = "Arial Unicode MS"
ActiveChart.ChartType = xlColumnClustered
ActiveChart.Location Where:=xlLocationAsObject, Name:=ActiveSheet.Name
ActiveChart.SetSourceData Source:=Sheets("PivotRevenue").Range("A4:B18")

With ActiveChart.Parent
.Left = 250
.Top = 200
.Width = 600
.Height = 500
End With
End Sub
Private Sub OptionButton1_Click()
'volume on intruemnt level
On Error Resume Next
ActiveSheet.ChartObjects.Delete

Dim objCht As ChartObject
On Error Resume Next
Set objCht = ActiveSheet.ChartObjects("Challenges")
If Not objCht Is Nothing Then
objCht.Delete
End If

ActiveSheet.Shapes.AddChart.Select
ActiveChart.Parent.Name = "Challenges"
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Text = Worksheets("Dashboard").Range("E9")
ActiveChart.ChartTitle.Font.Size = 12
ActiveChart.ChartTitle.Font.Name = "Arial Unicode MS"
ActiveChart.ChartType = xlColumnClustered
ActiveChart.Location Where:=xlLocationAsObject, Name:=ActiveSheet.Name
ActiveChart.SetSourceData Source:=Sheets("PivotTrade").Range("A6:CN20")

With ActiveChart.Parent
.Left = 250
.Top = 200
.Width = 600
.Height = 500
End With
End Sub

Private Sub OptionButton3_Click()
'volume on account level
On Error Resume Next
ActiveSheet.ChartObjects.Delete

Dim objCht As ChartObject
On Error Resume Next
Set objCht = ActiveSheet.ChartObjects("Challenges")
If Not objCht Is Nothing Then
objCht.Delete
End If

ActiveSheet.Shapes.AddChart.Select
ActiveChart.Parent.Name = "Challenges"
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Text = Worksheets("Dashboard").Range("E9")
ActiveChart.ChartTitle.Font.Size = 12
ActiveChart.ChartTitle.Font.Name = "Arial Unicode MS"
ActiveChart.ChartType = xlColumnClustered
ActiveChart.Location Where:=xlLocationAsObject, Name:=ActiveSheet.Name
ActiveChart.SetSourceData Source:=Sheets("PivotAccount").Range("A3:L18")

With ActiveChart.Parent
.Left = 250
.Top = 200
.Width = 600
.Height = 500
End With
End Sub

p45cal
08-30-2013, 01:39 AM
The following might work depending on whether the cell Dashboard!E9 updates or not on client selection; try changing:
ActiveChart.ChartTitle.Text = Worksheets("Dashboard").Range("E9")
to:
ActiveChart.ChartTitle.Caption = "=Dashboard!R9C5"
in all three subs.

Nmarkit
08-30-2013, 01:40 AM
cross posted at

http://www.mrexcel.com/forum/excel-questions/723370-chart-title-not-updating.html

Nmarkit
08-30-2013, 01:43 AM
That worked! thanks a ton p45cal!