Setting axis labels

[Robert H]

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!

 

[Robert H]

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

 

[OssieMac]

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

 

[Robert H]

Mac, is rngSeries the location of your labels?
thanks for helping!

 

[OssieMac]

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?

 

[Robert H]

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