• 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.

Macro to generate chart with parameters linked to cells

D

dourpil

Member
Hi all! This is a question I've asked on another forum but I'm taking the liberty of asking it here aswell since I don't seem to be getting any reply.

I'd like to easily generate a XY scatter chart. Ideally the parameters of the chart would be referenced to cells so that they can easily be changed everytime a chart is generated.
The reason for this is that I'd like this file to serve as a template where I could copy/paste a big range of data and select the series I want (or do not want) charted.
I'm very new to VBA so I don't really know where to start but I found online a macro that'll create a chart (that's a beginning..!^^)
It creates a chart from a selected range though so that's not what I'm looking for but it could be a starting point..

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
With ActiveSheet.ChartObjects("Chart 1").Chart ' Category (X) Axis
With .Axes(xlCategory)
If ActiveSheet.Range("$E$2").Value > .MinimumScale Then
.MaximumScale = ActiveSheet.Range("$E$2").Value
.MinimumScale = ActiveSheet.Range("$E$3").Value
Else
.MinimumScale = ActiveSheet.Range("$E$3").Value
.MaximumScale = ActiveSheet.Range("$E$2").Value
End If
.MajorUnit = ActiveSheet.Range("$E$4").Value
End With ' Value (Y) Axis
With .Axes(xlValue)
If ActiveSheet.Range("$F$2").Value > .MinimumScale Then
.MaximumScale = ActiveSheet.Range("$F$2").Value
.MinimumScale = ActiveSheet.Range("$F$3").Value
Else
.MinimumScale = ActiveSheet.Range("$F$3").Value
.MaximumScale = ActiveSheet.Range("$F$2").Value
End If
.MajorUnit = ActiveSheet.Range("$F$4").Value
End With
End With
End Sub

I also found a piece of code to work with the axis:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
With ActiveSheet.ChartObjects("Chart 1").Chart ' Category (X) Axis
With .Axes(xlCategory)
If ActiveSheet.Range("$E$2").Value > .MinimumScale Then
.MaximumScale = ActiveSheet.Range("$E$2").Value
.MinimumScale = ActiveSheet.Range("$E$3").Value
Else
.MinimumScale = ActiveSheet.Range("$E$3").Value
.MaximumScale = ActiveSheet.Range("$E$2").Value
End If
.MajorUnit = ActiveSheet.Range("$E$4").Value
End With ' Value (Y) Axis
With .Axes(xlValue)
If ActiveSheet.Range("$F$2").Value > .MinimumScale Then
.MaximumScale = ActiveSheet.Range("$F$2").Value
.MinimumScale = ActiveSheet.Range("$F$3").Value
Else
.MinimumScale = ActiveSheet.Range("$F$3").Value
.MaximumScale = ActiveSheet.Range("$F$2").Value
End If
.MajorUnit = ActiveSheet.Range("$F$4").Value
End With
End With
End Sub

So I guess I could play with the E2-E3-F2-F3 cells and replace them with my references (B4-B5-B6-B7).. Maybe? But it seems this macro is designed to be ran after the chart has been generated.

Also found this for the title:

Code: 
With Sheet1.ChartObjects(1).Chart
.HasTitle = True
.ChartTitle.Text ="=Sheet1!R1C3"
End With

But same remark and I have no idea what the "R1C3" could refer to

Thanks in advance for the help
Have a great day,

Simon
 

Attachments

  • generate chart.xlsx
    10.6 KB · Views: 4
  • generate chart.xlsx
    10.6 KB · Views: 1
Last edited by a moderator:
Dourpil

Firstly, Welcome to the Chandoo.org Forums

Are you aware of Chart Templates?

1. Setup a chart exactly as you want
2. Select the Chart,
3. Goto the Design tab, Save as Template

Now you can select a new range of Data
1. Got the Insert Tab
2. All Chart types
3. Select templates in the left pane
4. Select your template
5. Enjoy
 
Hi Hui (that's pretty weird to write!),
Thanks for your welcome and your reply.

I know of chart templates and I've been using them at times but in this case they have a few disadvantages:
- A template will memorize the amount of series I've charted. If I want to chart more series, the series formatting will change and thus I'll have to do changes manually.
- Same for the axis I believe
- In this instance I'd copy/paste a table in my data entry tab, then select the name of the series I want to chart. I can easily ignore 70% of the columns if need be. This can't effectively be done with a template as I'll still have to either select the whole table then deleted series, or directly select the series I need.

All this would cause a waste of time, and time is what I want to save with this chart-creating-thingy thing :)
 
Hi ,

Can you specify what exactly will be the configurables ? What are the changes you will make from one chart to another ? If we know this , then the code that you have posted can be modified to accept these as inputs and use them appropriately within the code.

Narayan
 
Hi Narayank and thank you for your reply,

The most changing configurables are the first 7 rows of the file I attached to my 1st post:
I'd like the title of the chart (could consider it optional though)
The X series name would be the header of the column I want as X
The Y series names could be the headers of the columns I want plotted. I guess this would be the trickiest part as I'd sometimes want to plot 5 series and sometimes 10.
And then the axis (min and max values) as the automatic axis in Excel can sometimes be weird.

I can't evaluate how relevant the code I posted is to my request as, like I said, I have no experience with VBA yet.

Simon
 
Hi!

I've come accross this page before. It looks good but I don't feel it could fulfill the roles I'm looking for...
 
You must log in or register to reply here.
Back
Top