VBA: Create, Select Data, and Format Embedded Scatterplot

[poweraspect1]

Hello,

I am entirely new to VBA having never coded anything before. I am attempting to create a macro which will create an XY Scatterplot embedded in an existing sheet. I have searched around for several hours now and pieced together quite a few half-solutions that don't quite work or mesh together very well. There seems to be a plethora of ways to create the chart and define it's characteristics and I am spinning my wheels now.

The relevant points are below:

1. I will have 3 or more distinct series with their own X and Y values.
2. Some series data is stored in the same sheet as I want the plot to be in and some on separate sheets of the same workbook.
3. I must be able to define the series names in the code (not a cell reference).
4. The plot needs to be placed in a precise location within the sheet.
5. The plot size must be defined in the code.
6. The plot needs to have the series colors formatted as I see fit.
7. The plot must have the title and both the x and y axis names defined.
8. A legend must be included
9. I will run this in multiple workbooks which have the same sheet names and data format/location.

In the example below I show the desired chart I would like VBA to produce. I would like it to be located in the range B19:O39 on the "Blue" sheet. The Blue sheet would be the active sheet the macro is run from. Data is pulled for the Blue and Green series from the active sheet and the red series from the "Red" sheet.

If additional info is required please let me know and I will update this post.

All suggestions are greatly appreciated! While a finished code would be wonderful I'm hoping any answers could help to explain what each line actually does so I can develop and adapt the solution myself. Also if anyone knows of a good learners book for VBA I can pick up for future use I'd love the suggestions.

[/URL][/IMG]

[/URL][/IMG]

 

[Shankspony]

If you're having trouble with managing/interpreting the attributes and members of a chart object, try recording a macro of you manually creating a chart. Then look at the code that is generated and some questions might be answered. Reviewing recorded macros has helped me greatly. Looking at your requirements, everything you want can be done.

 

[poweraspect1]

If you're having trouble with managing/interpreting the attributes and members of a chart object, try recording a macro of you manually creating a chart. Then look at the code that is generated and some questions might be answered. Reviewing recorded macros has helped me greatly. Looking at your requirements, everything you want can be done.

One of the biggest issues I've had with recording it is that the chart is auto named "Chart 1" or "Chart 7" or whatever number chart it happens to be in that workbook. I'm not sure how to create a chart while simultaneously defining its name. In each of the workbooks I will be using this in there are existing charts, sometimes 2-3 charts, sometimes 10. Therefore I can't code something that says AddChart2.....Select "Chart 7", then change Chart 7's name to "Population". The created chart might be "Chart 8" in the next workbook I run the macro in and it won't work then.