Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Charting Question
I have a table of data which I create a scatter graph to show the points. Is
there an easy way to add labels through VBA? For eaxample if I have the data below in a spreadsheet and I want column B to be the X-axis and column C to be the Y-axis how can I easily/automatically add the text in column A as the data label? Also, oes it matter what order the data is sorted in? I want to sort by column A. Thanks A B C 1 Florida 2.5 500 2 Maine 2.7 300 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Charting Question
sfrancoe wrote:
I have a table of data which I create a scatter graph to show the points. Is there an easy way to add labels through VBA? For eaxample if I have the data below in a spreadsheet and I want column B to be the X-axis and column C to be the Y-axis how can I easily/automatically add the text in column A as the data label? Also, oes it matter what order the data is sorted in? I want to sort by column A. Thanks A B C 1 Florida 2.5 500 2 Maine 2.7 300 ------------------------- Have you tried to turn on the macro recorder, create the chart you want with everything included and formatted then turn off the recorder and look at the VBA code it created? I'd start from there. Bill |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Charting Question
The problem with that is that the chart wizard doesnt give you the ability to
assign a different value to the labels other than x value or y value. Once the chart is created there is no way that I can see to assign a different column to be the data labels. "Bill Martin" wrote: sfrancoe wrote: I have a table of data which I create a scatter graph to show the points. Is there an easy way to add labels through VBA? For eaxample if I have the data below in a spreadsheet and I want column B to be the X-axis and column C to be the Y-axis how can I easily/automatically add the text in column A as the data label? Also, oes it matter what order the data is sorted in? I want to sort by column A. Thanks A B C 1 Florida 2.5 500 2 Maine 2.7 300 ------------------------- Have you tried to turn on the macro recorder, create the chart you want with everything included and formatted then turn off the recorder and look at the VBA code it created? I'd start from there. Bill |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Charting Question
Actually it allows you to do anything you want. Anything you can add manually
to a chart you can record to see how VBA does it. In the SourceDataSeries section of creating the chart you can fill in a data name. And that does indeed get recorded into VBA by the macro recorder. Here's what I recorded for a trivial example with two data lines: range("A2:C21").Select Charts.Add ActiveChart.ChartType = xlXYScatter ActiveChart.SetSourceData Source:=Sheets("Test").range("A2:C21"), PlotBy:= _ xlColumns ActiveChart.SeriesCollection(1).Name = "=""First Data Line""" ActiveChart.SeriesCollection(2).Name = "=""Second Data Line""" ActiveChart.Location Whe=xlLocationAsObject, Name:="Test" Alternatively you could put the names of the plot lines in a cell, and just put the cell address into the Series title box to automatically pull it off the spreadsheet. Bill ---------------------------- sfrancoe wrote: The problem with that is that the chart wizard doesnt give you the ability to assign a different value to the labels other than x value or y value. Once the chart is created there is no way that I can see to assign a different column to be the data labels. "Bill Martin" wrote: sfrancoe wrote: I have a table of data which I create a scatter graph to show the points. Is there an easy way to add labels through VBA? For eaxample if I have the data below in a spreadsheet and I want column B to be the X-axis and column C to be the Y-axis how can I easily/automatically add the text in column A as the data label? Also, oes it matter what order the data is sorted in? I want to sort by column A. Thanks A B C 1 Florida 2.5 500 2 Maine 2.7 300 ------------------------- Have you tried to turn on the macro recorder, create the chart you want with everything included and formatted then turn off the recorder and look at the VBA code it created? I'd start from there. Bill |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Charting Question
thanks but it still is not working. I want each point to have its own label
which is in the column next to the x-axis data. So it would go like this.. coulmn A = Label column B = X-Axis Value column C = Y-Axis Value as i add data to a dynamic range i want the point on the scatter chart to have the label associated with the data in columns b and c. "Bill Martin" wrote: Actually it allows you to do anything you want. Anything you can add manually to a chart you can record to see how VBA does it. In the SourceDataSeries section of creating the chart you can fill in a data name. And that does indeed get recorded into VBA by the macro recorder. Here's what I recorded for a trivial example with two data lines: range("A2:C21").Select Charts.Add ActiveChart.ChartType = xlXYScatter ActiveChart.SetSourceData Source:=Sheets("Test").range("A2:C21"), PlotBy:= _ xlColumns ActiveChart.SeriesCollection(1).Name = "=""First Data Line""" ActiveChart.SeriesCollection(2).Name = "=""Second Data Line""" ActiveChart.Location Whe=xlLocationAsObject, Name:="Test" Alternatively you could put the names of the plot lines in a cell, and just put the cell address into the Series title box to automatically pull it off the spreadsheet. Bill ---------------------------- sfrancoe wrote: The problem with that is that the chart wizard doesnt give you the ability to assign a different value to the labels other than x value or y value. Once the chart is created there is no way that I can see to assign a different column to be the data labels. "Bill Martin" wrote: sfrancoe wrote: I have a table of data which I create a scatter graph to show the points. Is there an easy way to add labels through VBA? For eaxample if I have the data below in a spreadsheet and I want column B to be the X-axis and column C to be the Y-axis how can I easily/automatically add the text in column A as the data label? Also, oes it matter what order the data is sorted in? I want to sort by column A. Thanks A B C 1 Florida 2.5 500 2 Maine 2.7 300 ------------------------- Have you tried to turn on the macro recorder, create the chart you want with everything included and formatted then turn off the recorder and look at the VBA code it created? I'd start from there. Bill |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Charting Question
I don't understand what you're after, but the question is whether you can
manually set up a chart the way you want it? If so, you can record the process and thereafter do it from VBA. If you can't set up what you want manually even, then you can't do it from VBA. Bill ----------------------- sfrancoe wrote: thanks but it still is not working. I want each point to have its own label which is in the column next to the x-axis data. So it would go like this.. coulmn A = Label column B = X-Axis Value column C = Y-Axis Value as i add data to a dynamic range i want the point on the scatter chart to have the label associated with the data in columns b and c. "Bill Martin" wrote: Actually it allows you to do anything you want. Anything you can add manually to a chart you can record to see how VBA does it. In the SourceDataSeries section of creating the chart you can fill in a data name. And that does indeed get recorded into VBA by the macro recorder. Here's what I recorded for a trivial example with two data lines: range("A2:C21").Select Charts.Add ActiveChart.ChartType = xlXYScatter ActiveChart.SetSourceData Source:=Sheets("Test").range("A2:C21"), PlotBy:= _ xlColumns ActiveChart.SeriesCollection(1).Name = "=""First Data Line""" ActiveChart.SeriesCollection(2).Name = "=""Second Data Line""" ActiveChart.Location Whe=xlLocationAsObject, Name:="Test" Alternatively you could put the names of the plot lines in a cell, and just put the cell address into the Series title box to automatically pull it off the spreadsheet. Bill ---------------------------- sfrancoe wrote: The problem with that is that the chart wizard doesnt give you the ability to assign a different value to the labels other than x value or y value. Once the chart is created there is no way that I can see to assign a different column to be the data labels. "Bill Martin" wrote: sfrancoe wrote: I have a table of data which I create a scatter graph to show the points. Is there an easy way to add labels through VBA? For eaxample if I have the data below in a spreadsheet and I want column B to be the X-axis and column C to be the Y-axis how can I easily/automatically add the text in column A as the data label? Also, oes it matter what order the data is sorted in? I want to sort by column A. Thanks A B C 1 Florida 2.5 500 2 Maine 2.7 300 ------------------------- Have you tried to turn on the macro recorder, create the chart you want with everything included and formatted then turn off the recorder and look at the VBA code it created? I'd start from there. Bill |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Charting Question
HI sfrancoe,
Chart Labeler (by Rob Bovey) http://appspro.com Chart Tools by (John Walkenbach) http://j-walk.com/ss Label Chart Points (by me) http://www.edferrero.com/charting.aspx Ed Ferrero Microsoft Excel MVP http://www.edferrero.com I have a table of data which I create a scatter graph to show the points. Is there an easy way to add labels through VBA? For eaxample if I have the data below in a spreadsheet and I want column B to be the X-axis and column C to be the Y-axis how can I easily/automatically add the text in column A as the data label? Also, oes it matter what order the data is sorted in? I want to sort by column A. Thanks A B C 1 Florida 2.5 500 2 Maine 2.7 300 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Charting Question
Hi Ed
Thanks for the links and I am sure they work fine but the problem is that I am trying to get this solution at work and my company won't let me download any third party addins. I have some code that actually automatically adds labels (see below) but my problem is that it appears the data has to be sorted in ascending order by X value. Otherwise the labels don't match up correctly. Sub LittleChart_AttachLabelsToPoints() ' Dimension Variables. Dim Counter As Integer, ChartName As Variant Dim SourceWorksheet As Variant, xVals As Variant, xCell As Variant Dim xLabel As Variant Dim miniChart As ChartObject On Error GoTo attachError Set miniChart = ActiveSheet.ChartObjects(1) ' Store the name of the active chart in "ChartName". ChartName = miniChart.Name ' Store the definition of the first series in "xVals". xVals = miniChart.Chart.SeriesCollection(1).Formula ' These lines extract the name of the source worksheet from xVals. SourceWorksheet = Left(xVals, InStr(1, xVals, "!") - 1) SourceWorksheet = Right(SourceWorksheet, Len(SourceWorksheet) - InStr(1, SourceWorksheet, "(")) If Left(SourceWorksheet, 1) = "," Then SourceWorksheet = Right(SourceWorksheet, Len(SourceWorksheet) - 1) End If ' Replace the actual source worksheet name with "xlsheet" so that the ' searches that follow will work correctly if the worksheet name ' contains commas. xVals = Application.Substitute(xVals, SourceWorksheet, "xlSheet") ' More Processing of the xVals name. xVals = Right(xVals, Len(xVals) - InStr(1, xVals, ",")) ' If the chart is using "assumed" x-values then show an alert ' message. If Left(xVals, 1) = "," Then MsgBox "This X-Y scatter chart is using assumed x values. The macro cannot contine." ' Exit the subroutine if "assumed" x-values are in use. Exit Sub End If ' More processing of the xVals name. xVals = Left(xVals, InStr(1, xVals, ",") - 1) ' Put the original source worksheet name back into xVals, replacing ' "xlSheet". xVals = Application.Substitute(xVals, "xlSheet", SourceWorksheet) ' Initialize a counter. Counter = 1 ' For each cell in the range xVals... For Each xCell In Range(xVals) 'Get the value of the label next to the current x-value. xLabel = xCell.Offset(0, -1).Value If Len(xLabel) < 1 Then Exit Sub End If 'Attach a label to the current data point in the chart. miniChart.Chart.SeriesCollection(1).Points(Counter ).HasDataLabel = True 'Put the text("DataPoint1",for example) into the attached label. miniChart.Chart.SeriesCollection(1).Points(Counter ).DataLabel.Text = xLabel 'Increment the counter. Counter = Counter + 1 Next xCell 'loop until all done Done: 'Clean variables Set miniChart = Nothing ActiveChart.SeriesCollection(1).DataLabels.Select Selection.Font.Bold = True With Selection.Font .Name = "Arial" .Size = 9 End With Exit Sub attachError: MsgBox Err.Number & Err.Description, vbOKOnly GoTo Done ActiveChart.SeriesCollection(1).DataLabels.Select Selection.Font.Bold = True With Selection.Font .Name = "Arial" .Size = 8 End With End Sub "Ed Ferrero" wrote: HI sfrancoe, Chart Labeler (by Rob Bovey) http://appspro.com Chart Tools by (John Walkenbach) http://j-walk.com/ss Label Chart Points (by me) http://www.edferrero.com/charting.aspx Ed Ferrero Microsoft Excel MVP http://www.edferrero.com I have a table of data which I create a scatter graph to show the points. Is there an easy way to add labels through VBA? For eaxample if I have the data below in a spreadsheet and I want column B to be the X-axis and column C to be the Y-axis how can I easily/automatically add the text in column A as the data label? Also, oes it matter what order the data is sorted in? I want to sort by column A. Thanks A B C 1 Florida 2.5 500 2 Maine 2.7 300 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Charting Question
Hi sfrancoe,
Here is the code I use. Sub LabelPoints() Dim pts As Points Dim LabelRange As Range Dim stRow As Integer Dim stCol As Integer Dim i As Integer ' This example assumes that data and data labels are in columns ' use the input box function to allow the user to select a range Set LabelRange = Application.InputBox(prompt:="Enter Range containing Labels" _ & Chr(13), Type:=8) stRow = LabelRange.Row stCol = LabelRange.Column ' you could prompt for chart name here Set pts = ActiveSheet.ChartObjects("Chart 1").Chart. _ SeriesCollection(1).Points ' now show the data label for each point and set the text ' to that in the selected range For i = 1 To pts.Count pts(i).ApplyDataLabels Type:=xlShowValue pts(i).DataLabel.Text = "=Sheet1!R" & i + stRow - 1 & "C" & stCol Next i End Sub Ed Ferrero Microsoft Excel MVP http://www.edferrero.com Hi Ed Thanks for the links and I am sure they work fine but the problem is that I am trying to get this solution at work and my company won't let me download any third party addins. I have some code that actually automatically adds labels (see below) but my problem is that it appears the data has to be sorted in ascending order by X value. Otherwise the labels don't match up correctly. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Charting Question | Charts and Charting in Excel | |||
Pivot table question | Excel Discussion (Misc queries) | |||
Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions | |||
Simple charting question | Charts and Charting in Excel | |||
Dynamic charting problems (events) | Charts and Charting in Excel |