XY Chart - Category Labels through VBA

I have created a short VBA macro that reads x and y data from a spreadsheet, 
where every row represents a new series.  Besides the x and y data colums, I 
also have columns that specify the size of the marker, the markerstyle, and 
the foreground and background colors.  I have adapted some of John 
Walkenbach's code to label each of the datapoints using .name.

The x axis is basically an integer 1 to 5 and the y axis is a value like 
price or variable margin. There usually are multiple datapoints on any given 
integer on the x axis.  For example, I may be plotting the price that a 
customers get in three different countries (US, Canada, and Mexico).  I 
arrange all my data items with the x value equal to 1 (for the US), 2(for 
Canada) and 3(for Mexico) along with the corresponding y value equal to the 
price that customer receives.

Ploting this results in the graph I want to see, except with 1, 2 and 3 as 
the category labels for the x axis.  I would like to be able to change the x 
axis label to US, Canada, and Mexico but still keep my x values at 1, 2, and 
3.  I also want to do this within the VBA routine where the other 
seriescollection information is determined.

Please note ... each datapoint is its own series.  Any ideas?

Thanks

Frank


0
hayesfj (19)
10/12/2004 1:47:16 AM
excel.charting 18370 articles. 0 followers. Follow

4 Replies
394 Views

Similar Articles

[PageSpeed] 5

Frank -

Start by plotting a dummy series, with X values {US, Canada, Mexico} and Y values 
{0, 0, 0}. Make it a column chart. Then add your one-point series to the chart, 
making each an XY Scatter series. Excel may automatically add secondary axes for the 
XY series, but you can remove them and the chart will use the primary axes, plotting 
1,2,3 over the X categories.

You could also create a dummy axis with a dummy series:

   http://peltiertech.com/Excel/Charts/ArbitraryAxis.html
   http://peltiertech.com/Excel/Charts/Y_CategoryAxis.html

But I think a dummy column chart series is easier.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Frank & Pam Hayes wrote:

> I have created a short VBA macro that reads x and y data from a spreadsheet, 
> where every row represents a new series.  Besides the x and y data colums, I 
> also have columns that specify the size of the marker, the markerstyle, and 
> the foreground and background colors.  I have adapted some of John 
> Walkenbach's code to label each of the datapoints using .name.
> 
> The x axis is basically an integer 1 to 5 and the y axis is a value like 
> price or variable margin. There usually are multiple datapoints on any given 
> integer on the x axis.  For example, I may be plotting the price that a 
> customers get in three different countries (US, Canada, and Mexico).  I 
> arrange all my data items with the x value equal to 1 (for the US), 2(for 
> Canada) and 3(for Mexico) along with the corresponding y value equal to the 
> price that customer receives.
> 
> Ploting this results in the graph I want to see, except with 1, 2 and 3 as 
> the category labels for the x axis.  I would like to be able to change the x 
> axis label to US, Canada, and Mexico but still keep my x values at 1, 2, and 
> 3.  I also want to do this within the VBA routine where the other 
> seriescollection information is determined.
> 
> Please note ... each datapoint is its own series.  Any ideas?
> 
> Thanks
> 
> Frank
> 
> 

0
10/13/2004 2:01:42 AM
Jon,

Thank you for the advice, but I can not seem to get this to work with 
multiple Y values in each x series.  Excel wants to treat each Y value in a 
separate X value.  To continue my example, there may be three customers in 
the US, four customers in Canada, and two customers in Mexico.  I want to 
show the variable margin for each customer, and I only want to have three x 
values.

Frank

"Jon Peltier" <DOjonNOTxlmvp@SPAMpeltiertechME.com> wrote in message 
news:%23jDCZiMsEHA.832@TK2MSFTNGP10.phx.gbl...
> Frank -
>
> Start by plotting a dummy series, with X values {US, Canada, Mexico} and Y 
> values {0, 0, 0}. Make it a column chart. Then add your one-point series 
> to the chart, making each an XY Scatter series. Excel may automatically 
> add secondary axes for the XY series, but you can remove them and the 
> chart will use the primary axes, plotting 1,2,3 over the X categories.
>
> You could also create a dummy axis with a dummy series:
>
>   http://peltiertech.com/Excel/Charts/ArbitraryAxis.html
>   http://peltiertech.com/Excel/Charts/Y_CategoryAxis.html
>
> But I think a dummy column chart series is easier.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
> Frank & Pam Hayes wrote:
>
>> I have created a short VBA macro that reads x and y data from a 
>> spreadsheet, where every row represents a new series.  Besides the x and 
>> y data colums, I also have columns that specify the size of the marker, 
>> the markerstyle, and the foreground and background colors.  I have 
>> adapted some of John Walkenbach's code to label each of the datapoints 
>> using .name.
>>
>> The x axis is basically an integer 1 to 5 and the y axis is a value like 
>> price or variable margin. There usually are multiple datapoints on any 
>> given integer on the x axis.  For example, I may be plotting the price 
>> that a customers get in three different countries (US, Canada, and 
>> Mexico).  I arrange all my data items with the x value equal to 1 (for 
>> the US), 2(for Canada) and 3(for Mexico) along with the corresponding y 
>> value equal to the price that customer receives.
>>
>> Ploting this results in the graph I want to see, except with 1, 2 and 3 
>> as the category labels for the x axis.  I would like to be able to change 
>> the x axis label to US, Canada, and Mexico but still keep my x values at 
>> 1, 2, and 3.  I also want to do this within the VBA routine where the 
>> other seriescollection information is determined.
>>
>> Please note ... each datapoint is its own series.  Any ideas?
>>
>> Thanks
>>
>> Frank
>>
>>
> 


0
hayesfj (19)
10/14/2004 12:04:15 AM
Frank -

If you add your additional points as XY Scatter series, all you need are the X 
values 1, 2, or 3. These will line up above the country names.

I made a very simplistic example to show you the steps:

   http://PeltierTech.com/Excel/Zips/ScatterCategoryAxisForFrank.zip

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Frank & Pam Hayes wrote:

> Jon,
> 
> Thank you for the advice, but I can not seem to get this to work with 
> multiple Y values in each x series.  Excel wants to treat each Y value in a 
> separate X value.  To continue my example, there may be three customers in 
> the US, four customers in Canada, and two customers in Mexico.  I want to 
> show the variable margin for each customer, and I only want to have three x 
> values.
> 
> Frank
> 
> "Jon Peltier" <DOjonNOTxlmvp@SPAMpeltiertechME.com> wrote in message 
> news:%23jDCZiMsEHA.832@TK2MSFTNGP10.phx.gbl...
> 
>>Frank -
>>
>>Start by plotting a dummy series, with X values {US, Canada, Mexico} and Y 
>>values {0, 0, 0}. Make it a column chart. Then add your one-point series 
>>to the chart, making each an XY Scatter series. Excel may automatically 
>>add secondary axes for the XY series, but you can remove them and the 
>>chart will use the primary axes, plotting 1,2,3 over the X categories.
>>
>>You could also create a dummy axis with a dummy series:
>>
>>  http://peltiertech.com/Excel/Charts/ArbitraryAxis.html
>>  http://peltiertech.com/Excel/Charts/Y_CategoryAxis.html
>>
>>But I think a dummy column chart series is easier.
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>Tutorials and Custom Solutions
>>http://PeltierTech.com/
>>_______
>>
>>Frank & Pam Hayes wrote:
>>
>>
>>>I have created a short VBA macro that reads x and y data from a 
>>>spreadsheet, where every row represents a new series.  Besides the x and 
>>>y data colums, I also have columns that specify the size of the marker, 
>>>the markerstyle, and the foreground and background colors.  I have 
>>>adapted some of John Walkenbach's code to label each of the datapoints 
>>>using .name.
>>>
>>>The x axis is basically an integer 1 to 5 and the y axis is a value like 
>>>price or variable margin. There usually are multiple datapoints on any 
>>>given integer on the x axis.  For example, I may be plotting the price 
>>>that a customers get in three different countries (US, Canada, and 
>>>Mexico).  I arrange all my data items with the x value equal to 1 (for 
>>>the US), 2(for Canada) and 3(for Mexico) along with the corresponding y 
>>>value equal to the price that customer receives.
>>>
>>>Ploting this results in the graph I want to see, except with 1, 2 and 3 
>>>as the category labels for the x axis.  I would like to be able to change 
>>>the x axis label to US, Canada, and Mexico but still keep my x values at 
>>>1, 2, and 3.  I also want to do this within the VBA routine where the 
>>>other seriescollection information is determined.
>>>
>>>Please note ... each datapoint is its own series.  Any ideas?
>>>
>>>Thanks
>>>
>>>Frank
>>>
>>>
>>
> 
> 

0
10/14/2004 4:21:41 AM
Thank you Jon ... I get it now!  I appreciate your going to the trouble of 
crafting the example.

Frank

"Jon Peltier" <DOjonNOTxlmvp@SPAMpeltiertechME.com> wrote in message 
news:epM8PVasEHA.2800@tk2msftngp13.phx.gbl...
> Frank -
>
> If you add your additional points as XY Scatter series, all you need are 
> the X values 1, 2, or 3. These will line up above the country names.
>
> I made a very simplistic example to show you the steps:
>
>   http://PeltierTech.com/Excel/Zips/ScatterCategoryAxisForFrank.zip
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
> Frank & Pam Hayes wrote:
>
>> Jon,
>>
>> Thank you for the advice, but I can not seem to get this to work with 
>> multiple Y values in each x series.  Excel wants to treat each Y value in 
>> a separate X value.  To continue my example, there may be three customers 
>> in the US, four customers in Canada, and two customers in Mexico.  I want 
>> to show the variable margin for each customer, and I only want to have 
>> three x values.
>>
>> Frank
>>
>> "Jon Peltier" <DOjonNOTxlmvp@SPAMpeltiertechME.com> wrote in message 
>> news:%23jDCZiMsEHA.832@TK2MSFTNGP10.phx.gbl...
>>
>>>Frank -
>>>
>>>Start by plotting a dummy series, with X values {US, Canada, Mexico} and 
>>>Y values {0, 0, 0}. Make it a column chart. Then add your one-point 
>>>series to the chart, making each an XY Scatter series. Excel may 
>>>automatically add secondary axes for the XY series, but you can remove 
>>>them and the chart will use the primary axes, plotting 1,2,3 over the X 
>>>categories.
>>>
>>>You could also create a dummy axis with a dummy series:
>>>
>>>  http://peltiertech.com/Excel/Charts/ArbitraryAxis.html
>>>  http://peltiertech.com/Excel/Charts/Y_CategoryAxis.html
>>>
>>>But I think a dummy column chart series is easier.
>>>
>>>- Jon
>>>-------
>>>Jon Peltier, Microsoft Excel MVP
>>>Peltier Technical Services
>>>Tutorials and Custom Solutions
>>>http://PeltierTech.com/
>>>_______
>>>
>>>Frank & Pam Hayes wrote:
>>>
>>>
>>>>I have created a short VBA macro that reads x and y data from a 
>>>>spreadsheet, where every row represents a new series.  Besides the x and 
>>>>y data colums, I also have columns that specify the size of the marker, 
>>>>the markerstyle, and the foreground and background colors.  I have 
>>>>adapted some of John Walkenbach's code to label each of the datapoints 
>>>>using .name.
>>>>
>>>>The x axis is basically an integer 1 to 5 and the y axis is a value like 
>>>>price or variable margin. There usually are multiple datapoints on any 
>>>>given integer on the x axis.  For example, I may be plotting the price 
>>>>that a customers get in three different countries (US, Canada, and 
>>>>Mexico).  I arrange all my data items with the x value equal to 1 (for 
>>>>the US), 2(for Canada) and 3(for Mexico) along with the corresponding y 
>>>>value equal to the price that customer receives.
>>>>
>>>>Ploting this results in the graph I want to see, except with 1, 2 and 3 
>>>>as the category labels for the x axis.  I would like to be able to 
>>>>change the x axis label to US, Canada, and Mexico but still keep my x 
>>>>values at 1, 2, and 3.  I also want to do this within the VBA routine 
>>>>where the other seriescollection information is determined.
>>>>
>>>>Please note ... each datapoint is its own series.  Any ideas?
>>>>
>>>>Thanks
>>>>
>>>>Frank
>>>>
>>>>
>>>
>>
>>
> 


0
hayesfj (19)
10/14/2004 10:38:55 AM
Reply:

Similar Artilces:

Can not get Excel macros to save chart format -lost in space!
Help??? I've been searching this forum for 5 days trying to find a problem similar to what I am having without any luck. I have been trying to use Excel macro to record the creation, formating and saving of a simple bar chart against my Excel data range but the macros do not run for the chart. I am able to creat the charts okay but the macro craps out when I run it. I keep getting " Set ActiveChart = ActiveSheet.ChartObjects(Chart1)" type errors as if it's not recognizing the chart that I just made. I am an advanced Excel person BUT not so with VB coding. ...

Help with Charts
:confused -------------------------------------------------------------------------------- I work in a Doctors surgery and I'm creating a Spread Sheet for select number of patients to monitor their blood results. I need about 30 charts on each patients sheet, To save me doing eac one seperately I have tryed to copy and paste them in to the nex patients details.... But all the charts refer to the previous patient details. Is there anyway of copying them so that they relate to th sheet they are copied in to? Thanx Alexandra Huckerby (fustrated and tired!! -- ~lexy -----------------...

Overlapping bars within chart
Sample scenario: Let's say in each year, I have this many cats: 2010: 6 2011: 8 2012: 20 Out of those cats, let's say that the following number have been neutered: 2010: 1 2011: 4 2012: 18 Is there a way for me to create a form of bar chart that shows the total number of cats per year as a "background" bar, with the total number of neutered cats in each year as a skinnier "foreground" bar that is a different color and overlays the background bar? I just figured this out on my own. As an FYI to anyone else, here was my solution: 1. Plot the ...

Chart Title or Text Box Linked to cell & sheet
I need to create charts from data collected via a com port. As i stands, the data is written to sheet 1. Chart 1 is ready to plot dat as it is written. Once the data is complete, I copy sheet 1 and char 1. This results in sheet 1(2) and Chart 1(2). All references to shee 1 on the newly created chart 1(2) automatically changes to refer t sheet 1(2). This applies to data ranges, Chart Title, Axis names. When creating text boxes refering to sheet1!$A$1, the reference doe not automacially change to sheet 1(2)!$A$1. Can this be done? If not, can I create multiple chart titles? Any help o...

Timeline bar chart
Hello, I am trying to create a bar chart based on key dates in a project. There are 20 different part numbers, all with individual timelines. Some parts of the process take many days, some one, etc. I want a bar chart that signifys these key dates, as well as shows a bar running throught the course of each individual aspect (ie, 12/2 - 12/8). I am having trouble doing this with dates. Any help would be greatly appreciated. -- shane476 ------------------------------------------------------------------------ shane476's Profile: http://www.excelforum.com/member.php?action=getinfo&u...

VBA speed question.
I want to know that A() and B() which faster... dim arr(99) as long dim arrB(9) as long sub A() arr(arrB()) = arr(arrB()) + 1 end sub sub B() dim X as long X = arrB() arr(X) = arr(X) + 1 end sub ...

Keep Pivot Table custom chart type
I'm creating a pivot table custom chart type "Line - Column on 2 Axes". The chart looks great when I create it. The problem is when the user does anything to the chart (filters the data, adds a new flield of data, etc.), the chart changes to where both data series are displayed as bar charts and the second axis is gone. I can't find an event that fires when they modify the chart, so I don't know that I can programatically change it back. Is there a way to keep it from changing in the first place? I'm using Excel 2003. Thanks! Loss of formatting is a known prob...

Rotating a line chart?
Is it possible to rotate a line chart so it looks like a vertical chart rather than a horizontal chart? And if so, once it is rotated, can one still interact with the chart and it's data? Thank you. Visit Excel Tips and Cases http://www.tushar-mehta.com/excel/tips/index.html and scroll down to 'Invert and rotate charts' The direct link http://www.tushar-mehta.com/excel/tips/0201-chart%20invert%20and% 20rotate.pdf is almost certain to change in the near future. The above applies to a XY Scatter chart. Hopefully, you can use that instead of a line chart. -- Regards, Tusha...

Simple charting question
I don't believe I'm having so much trouble with this. I'm trying to make a graph with Temperature (Celesius) as the X-axis and Baseball Speed (Km/hr) as the Y-axis. When I highlight the columns below and try to make a bar graph - 0 44.1 1 47 3 44.7 4 43.5 5 50.2 13 59.9 I get two series plotted or shown seperately. I would like to get one of the following: ...

Combining Stack bar and Line Charts
I'm trying to display 2 sets of data within the same chart; I want to display yr 1 data in a stacked bar format and yr 2 data in a line format. Both data sets would use the same axis; month and volume. I need to distinctively display each mo/yr together to show any increases or decreases in volume. Any suggestions you have are appreciated! -- tibor ------------------------------------------------------------------------ tibor's Profile: http://www.exceltip.com/forum/member.php?action=getinfo&userid=156 View this thread: http://www.exceltip.com/forum/showthread.php?threadid=1...

Add names to scatter plot chart
I have three columns of info in my excel spreadsheet. column 1 has names, column 2 has numbers and column 3 has numbers. I can create the scatter plot chart just fine, but what I am having a problem with is that I cannot get the names from column one to show next to the points on the scatter plot. How is this done? -- oberon.black ------------------------------------------------------------------------ oberon.black's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26732 View this thread: http://www.excelforum.com/showthread.php?threadid=471758 I'm gu...

How to automate payee to a certain category
Just downloaded 6 months of credit card charges. None of them imported a category. How can I automate say the 13 Shell Oil purchases in the register to have the same category like "gasoline" or at least have gasoline in the category when it is edited in the register? In microsoft.public.money, cl wrote: >Just downloaded 6 months of credit card charges. None of them imported >a category. How can I automate say the 13 Shell Oil purchases in the >register to have the same category like "gasoline" or at least have >gasoline in the category when it is edited i...

Chart
Chart - Title Widths Does anyone know how to find the width of a title/axis title? ...

Change Display Order in Charts
I have an X-Y chart with a cloud of points and a line. When the chart plots, the line is always hidden behind the cloud of points. Can I bring the line to the top so it becomes visible? I tried making the line first and last, but it always ends up behind the cloud. Thank you. Lines for a given chart group (group of data series) are plotted behind points of that same chart group in an XY chart. However, you can change the chart group by changing the axis on which the chart group is plotted. Make the plot with one series formatted as markers only and another as lines only, double cli...

Renaming a column label
How do I rename the column header labels from "A", "B", etc. to something else? Made a valiant effort. Can't figure it out. Mike -- DO NOT reply to the "from" address in this post. Instead, construct a reply address from this template: v6nos at yahoo dot com. Short answer: you don't - that capability (other than using numbers - check the Tools/Options/General R1C1 reference style checkbox) doesn't exist in XL. Longer answer: If you hide the row and column headers (Tools/Options/View) you can format Row 1 for column headers. You can even use t...

VBA turing up on the web...excel transfer...
Ok, here's the deal! I want to take my database/spreadsheet from excel and place it on the web with interactivity - I have done this so far.....what the problem is: I have macros in VBA programing that is not going with the excel pages onto the web...why? Is this going to be easier if I start from scratch in FrontPage to be able to control this program fully? Please help...I can't find anyone that knows....:-( ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Software! Free Support at http://www.ozgrid.com/forum/ ** ...

MS+Premium-Advanced Budget Category Not Found
I originally sent MSM support this question and they replied with only a partial answer but I couldn't reply to their email and they gave no direct link to respond so am trying here: Problem: I have a number of transactions in the banking section with the drop down category being "Legal Fees" (which I believe is a standard MSM category?) but when I try to add that category it to the advanced budget via Edit Budget/Add/"Add a category to a budget group from the list below" - "Legal Fees" does not appear in the drop down select box. And then when I try t...

combining charts from two spreadsheets
I hope someone can help me with this: I have two charts. The one thing these charts have in common are the addresses (the charts are from two different sources). On one chart, I will have monthly sales for the addresses. On another chart, I will have how much each location paid in expenses. I would like to combine these charts into one chart. The addresses on one chart will correspond to the addresses on the other chart and from their, I would like the other information to fall into place. Is this possible? Thanks for any help. this is totally out of the blue, but are you related to...

Using labels in EXCEL 2007
I have app 400 names in Col B row 1 down to row 400 of my spreadsheet. I would like to transfer that info to "paper labels" Anyone know how? Thanks "pcor" <pcor@discussions.microsoft.com> wrote in message news:F98982B9-75BD-4A9D-85AD-2C28BC9408EA@microsoft.com... >I have app 400 names in Col B row 1 down to row 400 of my spreadsheet. > I would like to transfer that info to "paper labels" > Anyone know how? > Thanks You ask "Anyone know how" Of course someone here knows how! I don't have your answer, b...

Updating Values and X Axis Labels
Thanks for your help. This response by XP wasn't expected. When ever I update the chart value range and X axis labels the chart (line graph) doesn't update. I click and drag to update the Value and Categoty X axis labels. Do I have the axsis formated incorrectly? Under the format labels, Right now I have "base unit" checked. And the Max and Min dates are entered. In previous versions of excel I know you could just update the cell range and the chart would update. This should be so elementary. Thanks - Tom -- Sony Luvy If the min and max are not set to au...

Bar chart with XY on secondary axis
I have four quarters of data that I display as a bar chart. I want to add additional data superimposed on each (XY) as a secondary axis. However, the XY coordinates are displayed on a single vertical axis in the center of the bars rather than on each related bar. Data: (BAR) 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr A 7.1 2.3 4.5 8.7 B 10.5 1.5 6.7 4.8 C 14.6 4.0 7.9 11.1 (XY) 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr A 25 55 67 ...

Using an image for a data label?
Greetings- I am trying to do something that I'm not sure if Excel is capable of doing. I have an XY Scatter plot that shows pairs of numerical values that correspond to a location on a digital image. Instead of having to manually find that x and y location on the image each time I am looking at a series of data, I'd like the data label OR the display box that appears when mousing over a data point for that series to be a small selection of that image that contains the x and y location of interest. This could be a manual process as well, though having a GUI or something similar to b...

Creating multiple charts
I am new to creating charts etc in Excel. I am compiling all the accident information for my church and I would like to have charts linked to this data to give a visual presentation of all the statistics collected. Can anyone tell me in plane english how I do this from a single large spreadsheet which contains all the data. select the data you want to use for the chart, click on the chart wizard icon on the toolbar & follow the wizard. -- John MOS Master Instructor Office 2000, 2002 & 2003 Please reply & rate any replies you get Ice Hockey rules (especially the Wightlink ...

In charts, is it possible to change data references to relevant?
Ive created a basic bar chart, but the speadsheet in which the data is held is constantly being updated. This is ruining my graph as the data references are absolute. Is there a way of changing the source data references to relevant? ive tried "f4" when in the graph "Source data", "values" option, but this does not work. Ive tried deleting all of the $ from the data, but they apear again when the dialogue box is closed. Charts use absolute references. But you can also use a reference to a dynamic name: http://peltiertech.com/Excel/Charts/Dynamics.html ...

labelling points in xy chart
I am using an XY chart as a simple map. The data are in three columns of a worksheet - X coordinate, Y coordinate and Point ID. On my chart I want to plot the points and have each labelled with the point ID. At the moment I can only make the labels show either the X coordinate value (if I select Show label in the Data Labels tab) or the Y coordinate value (if I select Show value). How can I automatically link the label text to the column with Point IDs? Grateful for any help. Hi have a look at the following add-ins which will do this for you as Excel does not support this automatica...