Coordinate graph

I am not sure what you are trying to do in your spreadsheet. If you want to plot data on an XY scatter chart, is there a reason you are not trying to use Excel's built in XY scatter chart? https://support.office.com/en-us/art...5-104a9018b86e

As a scientist, mathematician, and engineer, I think I understand fairly well what scatter plots do well and where they fall short. I don't yet understand why one of Excel's built in scatter plots won't work:

1) This should be the default behavior for a scatter plot. Choice of dot or dash or square or triangle or whatever are fairly easily controlled using the format series dialog.
2) As long as data does not change in the spreadsheet, they should not change in the chart. If you assign the X and Y values ranges appropriately, it should be easy to add as much data as you want to the chart (up to Excel's limitation which are quite large for 2010). When you are ready to clear the chart, simply clear the data range and start filling with new data.
3) The easiest way to "tag" a data point with a value/text from a range is to use Rob Bovey's XY chart labeler add-in, or to use 2013 or later, which has this functionality built into Excel.

Also we are looking at maybe having the item be colored a special color if both (axis's values are NEGITIVE) EXAMPLE x=-1, y=-5)

The easiest way I have found to "conditionally format" data points in a chart is to plot each "color" you want as a separate series http://peltiertech.com/conditional-f...-excel-charts/

I personally tend not to use the chart to calculate distances or rates, preferring instead to calculate those kinds of quantities in the spreadsheet. I can use a chart to visualize things and help see how to calculate any derived quantities I want to calculate, but I don't find the charts themselves very useful for computing quantities. Perhaps this is why you don't want to use the built in scatter plots, though I don't see how the spreadsheet you have is going to help with these (possibly because I do not yet understand everything about the project you are undertaking.

I don't have immediate access to Excel right now, but, perhaps if you walk through the analysis once, I can better understand what you are doing and can make better suggestions.

We have tried all day with the scatter plots excel give you and they will not work, as once we delete the x and y data these chart reset the size they are and this can not happen.

I cannot recreate this behavior. It might help to upload a sample file with what you are trying.

FWIW, here is a sample I made up (now that I am on a computer with Excel). I tried very quickly to match the scatter plot's size to the lines you drew on your spreadsheet, then fixed the axis limits to +/-50, crossing at 0. I can clear the data range, and the chart stays the same (except that the data points disappear). I can add varying numbers of data points within the data range, I could expand the data range to account for more data points. I could add the dates as data labels (if I had Rob Bovey's add-in on this computer). I think that, if I am going to understand what you need to do, I would need to understand what this chart does that you don't want it to do, or does not do that you need it to do.

Are you referring to the chart area or the plot area? I did not do anything to make either stay a certain size. For me, when I size the chart area and plot areas, they tend to stay that size. The only exception I find is using the insert/delete row/column commands. When executed on a row/column that goes through a chart, the chart will expand/contract with the inserted/deleted rows. In those cases, I simply have learned not to use the insert/delete row/column commands. Could that be part of your scenario -- using insert/delete row/column. (If there is any confusion at all, be sure to note that I am making an important distinction between the delete and clear commands).

If you are talking about the axes, I simply formatted each axis with the desired minimum and maximum values in the format axis dialog.

I am not aware of any way to stop it. As I noted, my solution for me is to be thoughtful about using the insert/delete row/column commands. If I don't want these kinds of effects to take place, I will look for a different combination of copy/cut/paste/etc. that will do the same thing as the insert/delete command. Or maybe I will be more careful about where I place the chart on the spreadsheet so that the chart will not overlap any rows/columns I will want to delete/insert between (Sometimes maybe even putting the chart on its own sheet and maybe creating a second window so I can see both the data and chart at the same time). Along those lines, one would need to know in some detail exactly how you are using the spreadsheet (editing adding and removing data while searching for patterns in the chart and so on) in order to make specific suggestions.

I could also see the possibility of some kind of event code that would detect these kinds of changes to the chart and correct them. Since this isn't the kind of programming I do, I am not sure what event would be appropriate. If you want to pursue this approach, someone else may need to step in who is more familiar with event code.

I use Rob Bovey's XY chart labeler add-in (appspro.com ), but I don't see why MS's code should not work. Can you explain what "does not work" means? Is it giving a compile error? A run-time error? It runs but does the wrong thing?.

I will see if someone else can come on board to talk about the auto-resize issue. I suspect that they will want to know in some detail how you are sizing the chart, and what are the critical elements of the chart size.

When I open and run the code (using F8 to step through line by line), I get the same error on the xVals = ActiveChart.SeriesCollection(1).Formula statement. Check what is active and I find that a cell in the sheet is active and not the chart. After selecting/activating the chart, it works just fine. You either need to make sure you activate the chart before the code runs, or adjust this (and any other "activechart" statements) to refer to the desired chart object on the sheet.

All you need do to fix the size is to right-click the chart, choose Format Chart Area and in the Properties section check the 'Do not move or size with cells' option. With VBA this would be thus

Please Login or Register  to view this content.