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!

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!

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.

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!

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!