Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting axis labels
Thinking that I need to look at the values from a Range instead of a
String, I tried this Dim xVal As String Dim xValRng As Range -code to setup xVal results in = B!$H$1,B!$L$1,B!$P$1,B!$T$1,B!$X$1,B!$AB$1,B!$AF$1 ,B!$AJ $1,B!$AN$1 Set xValRng = Range(xVal) .SeriesCollection(1).XValues = xValRng.Value this returns the value but only from the first cell in the range. a little information gathering: Debug.Print xValRng.Address = $H$1,$L$1,$P$1,$T$1,$X$1,$AB$1,$AF$1,$AJ$1,$AN$1 Debug.Print xValRng.Value = IMP_100_Hz (value of H1) so why only the first value and not all of them? More importantly how do I return all of them? Any help will be appreciated Robert On Dec 12, 6:36*pm, Robert H wrote: In chart creating code I am trying to set the x-axis labels using: .SeriesCollection(1).XValues = xVal xVal is a declared string that is basically an array of specific column headings selected *from many. When the chart is generated the x axis labels are the actual range values "B!$H$1, B!P$1, etc" *I need the labels to be the actual values from the cells. this seems a simple thing but it eludes me! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting axis labels
Hi Robert,
Loop through the series source range with for i = 1 to ... etc. like the following that I extracted and modified from one of my projects. The Point i on the chart is then is equivalent to the series source point i. In my project I actually use an offset of the series source which is a comment column to the right and I insert comments in the labels. Also I have not been able to do this without activating the chart. If you work out how to do that then let me know. Sub InsertChartLabels() Dim i As Long Dim rngSeries As Range With Sheets("Daily Data") Set rngSeries = .Range("E5:E15") End With Sheets("Graph").Select ActiveSheet.ChartObjects("Chart 1").Activate 'Following will remove any existing labels on chart 'Only use if populating selective labels say from offset comment column 'ActiveChart.SeriesCollection(1).HasDataLabels = False 'Loop through the cells of the series range For i = 1 To rngSeries.Count 'One column for the series 'The following inserts a label at individual points 'Point(i) corresponds to data cell(i) With ActiveChart.SeriesCollection(1).Points(i) .HasDataLabel = True 'Turn on data label for point. .DataLabel.Text = rngSeries.Cells(i) 'Populate data label. .DataLabel.Font.Name = "Arial" .DataLabel.Font.Size = 8 .DataLabel.Font.Bold = True End With Next i End Sub -- Regards, OssieMac "Robert H" wrote: Thinking that I need to look at the values from a Range instead of a String, I tried this Dim xVal As String Dim xValRng As Range -code to setup xVal results in = B!$H$1,B!$L$1,B!$P$1,B!$T$1,B!$X$1,B!$AB$1,B!$AF$1 ,B!$AJ $1,B!$AN$1 Set xValRng = Range(xVal) .SeriesCollection(1).XValues = xValRng.Value this returns the value but only from the first cell in the range. a little information gathering: Debug.Print xValRng.Address = $H$1,$L$1,$P$1,$T$1,$X$1,$AB$1,$AF$1,$AJ$1,$AN$1 Debug.Print xValRng.Value = IMP_100_Hz (value of H1) so why only the first value and not all of them? More importantly how do I return all of them? Any help will be appreciated Robert On Dec 12, 6:36 pm, Robert H wrote: In chart creating code I am trying to set the x-axis labels using: .SeriesCollection(1).XValues = xVal xVal is a declared string that is basically an array of specific column headings selected from many. When the chart is generated the x axis labels are the actual range values "B!$H$1, B!P$1, etc" I need the labels to be the actual values from the cells. this seems a simple thing but it eludes me! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting axis labels
Mac, is rngSeries the location of your labels?
thanks for helping! On Dec 12, 11:36*pm, OssieMac wrote: Hi Robert, Loop through the series source range with for i = 1 to ... etc. like the following that I extracted and modified from one of my projects. The Point i on the chart is then is equivalent to the series source point i. In my project I actually use an offset of the series source which is a comment column to the right and I insert comments in the labels. Also I have not been able to do this without activating the chart. If you work out how to do that then let me know. Sub InsertChartLabels() Dim i As Long Dim rngSeries As Range With Sheets("Daily Data") * * Set rngSeries = .Range("E5:E15") End With Sheets("Graph").Select ActiveSheet.ChartObjects("Chart 1").Activate 'Following will remove any existing labels on chart 'Only use if populating selective labels say from offset comment column 'ActiveChart.SeriesCollection(1).HasDataLabels = False 'Loop through the cells of the series range For i = 1 To rngSeries.Count * *'One column for the series * * 'The following inserts a label at individual points * * 'Point(i) corresponds to data cell(i) * * With ActiveChart.SeriesCollection(1).Points(i) * * * * .HasDataLabel = True * *'Turn on data label for point. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting axis labels
Hi Robert,
rngSeries is the range of actual data that is used to for the particular line on the graph. It does not include column header. On re-reading your original post I am now wondering if I am confused with what you really want to do. The code I gave you inserts data labels at each point on the actual graph. Is that what you want to do? -- Regards, OssieMac "Robert H" wrote: Mac, is rngSeries the location of your labels? thanks for helping! On Dec 12, 11:36 pm, OssieMac wrote: Hi Robert, Loop through the series source range with for i = 1 to ... etc. like the following that I extracted and modified from one of my projects. The Point i on the chart is then is equivalent to the series source point i. In my project I actually use an offset of the series source which is a comment column to the right and I insert comments in the labels. Also I have not been able to do this without activating the chart. If you work out how to do that then let me know. Sub InsertChartLabels() Dim i As Long Dim rngSeries As Range With Sheets("Daily Data") Set rngSeries = .Range("E5:E15") End With Sheets("Graph").Select ActiveSheet.ChartObjects("Chart 1").Activate 'Following will remove any existing labels on chart 'Only use if populating selective labels say from offset comment column 'ActiveChart.SeriesCollection(1).HasDataLabels = False 'Loop through the cells of the series range For i = 1 To rngSeries.Count 'One column for the series 'The following inserts a label at individual points 'Point(i) corresponds to data cell(i) With ActiveChart.SeriesCollection(1).Points(i) .HasDataLabel = True 'Turn on data label for point. .DataLabel.Text = rngSeries.Cells(i) 'Populate data label. .DataLabel.Font.Name = "Arial" .DataLabel.Font.Size = 8 .DataLabel.Font.Bold = True End With Next i End Sub -- Regards, OssieMac "Robert H" wrote: Thinking that I need to look at the values from a Range instead of a String, I tried this Dim xVal As String Dim xValRng As Range -code to setup xVal results in = B!$H$1,B!$L$1,B!$P$1,B!$T$1,B!$X$1,B!$AB$1,B!$AF$1 ,B!$AJ $1,B!$AN$1 Set xValRng = Range(xVal) .SeriesCollection(1).XValues = xValRng.Value this returns the value but only from the first cell in the range. a little information gathering: Debug.Print xValRng.Address = $H$1,$L$1,$P$1,$T$1,$X$1,$AB$1,$AF$1,$AJ$1,$AN$1 Debug.Print xValRng.Value = IMP_100_Hz (value of H1) so why only the first value and not all of them? More importantly how do I return all of them? Any help will be appreciated Robert On Dec 12, 6:36 pm, Robert H wrote: In chart creating code I am trying to set the x-axis labels using: .SeriesCollection(1).XValues = xVal xVal is a declared string that is basically an array of specific column headings selected from many. When the chart is generated the x axis labels are the actual range values "B!$H$1, B!P$1, etc" I need the labels to be the actual values from the cells. this seems a simple thing but it eludes me! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting axis labels
No, but I appreciate your responce. I was trying to fill the x-axis
labels. I worked around the problem by creating a named range of the non-contiguous cells and then filling the labels with the name. 'X-axis Labels Set xValRng = Range(xVal) 'setup named range for x-axis labels Names.Add Name:=dataTyp & "x_Lbl", RefersTo:=xValRng 'set name as x-axis label .SeriesCollection(1).XValues = "=" & shtNm & "!" & dataTyp & "x_Lbl" dataTyp is a string identifying the data type wich there are five types mixed together on the worksheet. Thanks again for the help Robert On Dec 13, 5:43*pm, OssieMac wrote: Hi Robert, rngSeries is the range of actual data that is used to for the particular line on the graph. It does not include column header. On re-reading your original post I am now wondering if I am confused with what you really want to do. The code I gave you inserts data labels at each point on the actual graph. Is that what you want to do? -- Regards, OssieMac "Robert H" wrote: Mac, is rngSeries the location of your labels? thanks for helping! On Dec 12, 11:36 pm, OssieMac wrote: Hi Robert, Loop through the series source range with for i = 1 to ... etc. like the following that I extracted and modified from one of my projects. The Point i on the chart is then is equivalent to the series source point i. In my project I actually use an offset of the series source which is a comment column to the right and I insert comments in the labels. Also I have not been able to do this without activating the chart. If you work out how to do that then let me know. Sub InsertChartLabels() Dim i As Long Dim rngSeries As Range With Sheets("Daily Data") * * Set rngSeries = .Range("E5:E15") End With Sheets("Graph").Select ActiveSheet.ChartObjects("Chart 1").Activate 'Following will remove any existing labels on chart 'Only use if populating selective labels say from offset comment column 'ActiveChart.SeriesCollection(1).HasDataLabels = False 'Loop through the cells of the series range For i = 1 To rngSeries.Count * *'One column for the series * * 'The following inserts a label at individual points * * 'Point(i) corresponds to data cell(i) * * With ActiveChart.SeriesCollection(1).Points(i) * * * * .HasDataLabel = True * *'Turn on data label for point. * * * * .DataLabel.Text = rngSeries.Cells(i) 'Populate data label. * * * * .DataLabel.Font.Name = "Arial" * * * * .DataLabel.Font.Size = 8 * * * * .DataLabel.Font.Bold = True * * End With Next i End Sub -- Regards, OssieMac "Robert H" wrote: Thinking that I need to look at the values from a Range instead of a String, I tried this Dim xVal As String Dim xValRng As Range -code to setup xVal results in = B!$H$1,B!$L$1,B!$P$1,B!$T$1,B!$X$1,B!$AB$1,B!$AF$1 ,B!$AJ $1,B!$AN$1 Set xValRng = Range(xVal) * * * * .SeriesCollection(1).XValues = xValRng.Value this returns the value but only from the first cell in the range. a little information gathering: Debug.Print xValRng.Address *= $H$1,$L$1,$P$1,$T$1,$X$1,$AB$1,$AF$1,$AJ$1,$AN$1 Debug.Print xValRng.Value *= IMP_100_Hz (value of H1) so why only the first value and not all of them? More importantly how do I return all of them? Any help will be appreciated Robert On Dec 12, 6:36 pm, Robert H wrote: In chart creating code I am trying to set the x-axis labels using: .SeriesCollection(1).XValues = xVal xVal is a declared string that is basically an array of specific column headings selected *from many. When the chart is generated the x axis labels are the actual range values "B!$H$1, B!P$1, etc" *I need the labels to be the actual values from the cells. this seems a simple thing but it eludes me! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Overlapping time (X) axis labels with auto axis options | Charts and Charting in Excel | |||
Excel 2007 text labels in category axis - missing labels | Charts and Charting in Excel | |||
xy-scatter-labels disappear when x-axis is changed into time-axis (XL07) | Charts and Charting in Excel | |||
Setting Y axis display labels | Charts and Charting in Excel | |||
setting x-axis labels | Charts and Charting in Excel |