• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using VBA to change the graph series values...Need help

B

bigjohn

New Member
Hi,


I get macro A to work but with same concept the macro B doesn't seem to work. What ever values added after = will be show in the graph series values. I have try function text and value but only show same result. Series value shows ='Sheet1'!$AD$22 instead of ='Sheet1'!$AQ$30:$AQ$90


Macro A:

ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue).MaximumScale = [R20]


Macro B:

ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(3).Values = [AD20]


Below is the macro I get from recording:


ActiveSheet.ChartObjects("Chart 1").Activate

ActiveChart.SeriesCollection(3).Name = "=""Line 1"""

ActiveChart.SeriesCollection(3).Values = "='Sheet1'!$AQ$30:$AQ$90"
 
The difference is that in this line

ActiveChart.SeriesCollection(3).Values = "='Sheet1'!$AQ$30:$AQ$90"`

The right hand sign of the equation contains a formula, whereas the max value is a simple value. I'm not sure what the correct syntax in your case would be, but I'm guessing it will need to be something like:

[pre]
Code: 
ActiveChart.SeriesCollection(3).Values = "='Sheet1'!" & [AQ30:AQ90].Address
[/pre]
^Above has not been tested, it's only an example of what the goal should sort of look like.
 
Luke M,


Is it possible to get the whole of <"='Sheet1'!" & [AQ30:AQ90].Address> to link to the spreadsheet like macro A?

If I need to change the range, I can instantly change from the spreadsheet instead of in the VBA.
 
Hi,


This is a renew question.


I am working on graph auto update for X-axis. Macro A is for graph format and Macro B is for X-axis.


Macro A:

ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue).MaximumScale = [R2]


Macro B:

ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(3).Values = [AD2]


AD2 = 'Sheet1'!$AQ$3:$AQ$9


Macro A is working but cant get the macro B to work. What ever values added after macro B's = will be show in the graph series values. So there are no link to the spreadsheet. Series value shows ='Sheet1'!$AD$2 instead of ='Sheet1'!$AQ$3:$AQ$9


Below is the macro I get from recording:


ActiveSheet.ChartObjects("Chart 1").Activate

ActiveChart.SeriesCollection(3).Name = "=""Line 1"""

ActiveChart.SeriesCollection(3).Values = "='Sheet1'!$AQ$3:$AQ$9"


May I know what syntax should be replacing [AD2] since it refer to a formula instead of a value? I get this from Luke M.


I need [AD2] to be link to the excel spreadsheet because any changes can be directly change in the spreadsheet.


Thank you.
 
Assuming that AD2 is displaying the literal text

Code: 
='Sheet1'!$AQ$3:$AQ$9


Code would be:

ActiveChart.SeriesCollection(3).Values = Worksheets("Sheet1").Range("AD2").Text
 
Luke M,


Although I am not very confidence when I first receive your answer, I got it working on the first try. Been looking for the answer for quite sometimes. Am happy that someone had helped me. Again big thank you.
 
Glad I was able to help out bigjohn. Let me know if there's anything I can do to help clarify.
 
You must log in or register to reply here.
Back
Top