Solved: creating Chart using Dynamic ranges..

[colene]

Project

sub project Jan 01 Jan 02 Jan 03
TP1 222 221 234
TP2 22 120 322
TP3 456 89 567
TP4 234 222 221




These values are on Sheet Data .I want to have a chart with this data .Here the year and the subprojects can vary in size for diffrent projects .

I have done the following code in Worksheet activate and my graph with dynamic ranges gets reflected only on the click of this sheet ..But I want it to happen on Chart_activate


Private Sub Worksheet_Activate()
Dim chtChart As Chart
Range("A3").Select
Selection.CurrentRegion.Select
myrange = Selection.Address
Set chtChart = Charts(1)
chtChart.ChartTitle.Text = "Chart with Dynamic ranges"
chtChart.SetSourceData (Sheets("Data").Range(myrange))
End Sub


Any suggestion please..Thanks in advance

[herzberg]

The code should be in the Chart_Activate event, since you want it to be triggered when the chart is selected. Put the below in the Chart_Activate event and try it out.

Private Sub Chart_Activate()
Dim SourceSheet As Worksheet
Dim Sourcerg As Range

Set SourceSheet = Sheets(1)
Set Sourcerg = SourceSheet.Range("A3").CurrentRegion

With Me
.HasTitle = True
.ChartTitle.Text = "Chart with Dynamic ranges"
.SetSourceData Source:=Sourcerg
End With

Set Sourcerg = Nothing
Set SourceSheet = Nothing

End Sub

[colene]

Wonderful....Thanks for the solution.