Dynamic Chart Range

[DJ]

Hi All,


I have more than 2 charts in my workbook and there is a chart for which I need to change source data range dynamically. I have done all the calculations using VBA code but the only problem is that I am unable to set this calculated range value to the chart property.


Reason for above problem is that it is not necessary that this chart is always active. However, in my google search, I am getting only one type of code which is referencing "ActiveChart", whereas as I mentioned above it is not necessary that this chart is always active. Below is the sample result I am getting online where Rng is a variable.

ActiveChart.SetSourceData Source:=Sheets("Dashboard").Range("W6:X" & Rng)


I also tried to use Chart name to change its source data range but got no success.


Kindly help with the correct line of code to change SourceData Range of a specific chart.


Thanks and regards,

DJ

 

[Hui]

DJ


The easiest way to make a dynamic chart is to use a Named Formula to either set the values via a formula or to store the values which are stored there via VBA


To help you further can you post a sample workbook refer tithe Sticky Green posts for directions

 

[DJ]

Hi Hui,


I have uploaded a file at https://www.rapidshare.com/#myrs_filemanager/file/4973


I was unable to upload actual file from office laptop yesterday so I have created a sample one and uploaded. There is a macro that I have created and assigned it to the combo box so as soon a user select the desired report view from the combo box drop down that macro will run and graph range will change accordingly. However, I don't know code to change graph range using VBA.


Please help me with the above mentioned problem.


Thanks and regards,

DJ

 

[DJ]

Hi Hui,


Sorry, I think link would be http://rapidshare.com/files/2536936423/GraphRangeProblem.xlsm


DJ

 

[NARAYANK991]

Hi DJ ,


Check out your file here :


http://speedy.sh/ADsFW/GraphRangeProblem.xlsm


One dynamic named range has been defined , and this is used in the chart. Sorry , but no VBA is involved.


Narayan

 

[DJ]

Hi Narayan,


That's a great solution given. I never thought Named Range can be a solution for this. Thank you very very much.


However, I would be grateful if you can help with VBA code too that I gave in my file. This is coz I really want to understand to change Chart range using VBA may be that can help me in future with some other report.


Thank you so much once again,

DJ

 

[Hui]

DJ

[pre]

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Values = "=Sheet1!$D$6:$D$12"
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!$B$6:$B$12"

[/pre]

 

[NARAYANK991]

Hi DJ ,


You can copy this into your workbook :

[pre]

Sub graphrange()
Dim i As Integer, LastRow As Integer

With ThisWorkbook.Worksheets("Sheet1")
LastRow = .Range("D6").End(xlDown).Row

For i = LastRow To 1 Step -1
If .Range("D6").Cells(i).Value <> "" Then
LastRow = .Range("D6").Cells(i).Row
Exit For
End If
Next

.ChartObjects("Chart 1").Chart.SetSourceData .Range("D6", .Range("D6").Cells(LastRow - 6 + 1))
.ChartObjects("Chart 1").Chart.SeriesCollection(1).XValues = .Range("B6", .Range("B6").Cells(LastRow - 6 + 1))
End With
End Sub

[/pre]
Narayan

 

[DJ]

Hi Narayan/Hui,


codes suggested are not working.


Narayan: I am unable to understand you VBA code and hence unable to modify it according to my need. Sorry I am not a VBA expert just a beginner only.


I tried to use Hui's code as it is simple for me to understand but still unable to modify it according to my need as I don't know about the chart properties.


I have pasted code below, plz check where I am making mistake:


ActiveSheet.ChartObjects("Chart 18").Activate

ActiveChart.SeriesCollection(1).Values = Sheets("Calculation").Range("W7:W" & VarRange)

ActiveChart.SeriesCollection(1).XValues = Sheets("Calculation").Range("A7:A" & VarRange)


Where VarRange is a variable value based on which I need to update my graph.


Please note that my data is in a different worksheet.


Thanks,

DJ

 

[NARAYANK991]

Hi DJ ,


Can you check out the workbook here ?


http://speedy.sh/EkptA/GraphRangeProblem.xlsm


I have removed the earlier defined named range , so when you select from the drop down , the chart will not update ; run the macro , and the chart will change correctly.


Narayan

 

[DJ]

Hi Narayan,


Thank you so much for your continuous help. However, I tried again with the Named Range method and successfully implemented it in my report with few changes.


I will check the latest file you have uploaded for me later.


Thank you so much once again.

DJ