What you could do is not use the built-in custom types, and instead build a
chart in which you apply the appropriate settings to each series. In other
words, make a line chart with all of the data, then specifically change the
axis group of the desired series to xlSecondary.

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


"ewan7279" wrote in message
...
My Colleague is German, therefore the chart type name would be different?
I
had not considered this. How could I alter the code to ensure the correct
chart type is selected please? Something like:

if 'English version of Excel' Then
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Lines on 2
Axes"
else
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="(German name
for)Lines on 2 Axes"
end if

Cheers,
Ewan

"Luca Brasi" wrote:

If still the ApplyCustomType method raises an error, it could be your
colleague uses another user interface language for Excel than English?
Or his chart gallery file had been edited or is just corrupt
(Xl8galry.xls)?



ewan7279 wrote:
Hi Joel,

thanks for the response. I tried your code and it works fine in my pc,
but
again my colleague cannot run it. Do you have any further ideas?

Cheers,
Ewan.

"Joel" wrote:

Your code idn't run on my version of excel 2003. I made some minor
changes
that should allow it to run on any PC


Private Sub CommandButton1_Click()

ActiveWorkbook.Unprotect ("BSCEwan")
Sheets("coal Charts").Activate


Dim Message, Title, Default, FirstValue, lastvalue
Message = "Enter first month" ' Set prompt.
Title = "First Month" ' Set title.
Default = "Jun-06" ' Set default.
' Display message, title, and default value.
FirstValue = InputBox(Message, Title, Default)

Dim FirstMonth, LastMonth As Range
With Worksheets("coal")
Set FirstMonth = .Columns("a:a").Find(FirstValue,
LookIn:=xlValues)
If FirstMonth Is Nothing Then
MsgBox "Value not available", vbOKOnly, "Error"
Exit Sub
End If
End With

Message = "Enter last month" ' Set prompt.
Title = "Last Month" ' Set title.
Default = "Jun-07" ' Set default.
' Display message, title, and default value.
lastvalue = InputBox(Message, Title, Default)

Application.ScreenUpdating = False

With Worksheets("coal").Columns("a:a")
Set LastMonth = .Find(lastvalue, LookIn:=xlValues)
If LastMonth Is Nothing Then
If LastMonth Is Nothing Then
MsgBox "Value not available", vbOKOnly, "Error"
Exit Sub
End If
End If
Set newchart = Charts.Add
newchart.SetSourceData Source:=Sheets("coal").Range(FirstMonth, _
LastMonth.Offset(0, 2)), PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).Name = "=""Total Direct Cost"""
ActiveChart.SeriesCollection(2).Name = "=""Direct Cost Per
Trade"""
ActiveChart.Location Whe=xlLocationAsObject, Name:="coal
Charts"
With ActiveChart.Parent
.Left = 75
.Width = 375
.Top = 25
.Height = 275
End With

ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Lines
on 2
Axes"
End With
End Sub

"ewan7279" wrote:

Hi all,

I have macros that create charts from lists of data. I can run these
macros
with no errors, yet when a colleague of mine tries in another
location, she
gets the above error message. Please, does anyone have any ideas
why? Could
it have something to do with the version of Excel? The charts
themselves are
custom 'lines on two axes' charts.

Here is a section of the code:

Private Sub CommandButton1_Click()

ActiveWorkbook.Unprotect ("BSCEwan")
Sheets("coal Charts").Activate


Dim Message, Title, Default, FirstValue, lastvalue
Message = "Enter first month" ' Set prompt.
Title = "First Month" ' Set title.
Default = "Jun-06" ' Set default.
' Display message, title, and default value.
FirstValue = InputBox(Message, Title, Default)

Dim FirstMonth, LastMonth As Range
With Worksheets("coal").Range("a:a")
Set FirstMonth = .Find(FirstValue, LookIn:=xlValues)
If Not FirstMonth Is Nothing Then
FirstMonth.Select
Else
MsgBox "Value not available", vbOKOnly, "Error"
Exit Sub
End If
End With

Message = "Enter last month" ' Set prompt.
Title = "Last Month" ' Set title.
Default = "Jun-07" ' Set default.
' Display message, title, and default value.
lastvalue = InputBox(Message, Title, Default)

Application.ScreenUpdating = False

With Worksheets("coal").Range("a:a")
Set LastMonth = .Find(lastvalue, LookIn:=xlValues)
If Not LastMonth Is Nothing Then
LastMonth.Offset(0, 2).Select
Charts.Add
ActiveChart.SetSourceData
Source:=Sheets("coal").Range(FirstMonth,
LastMonth.Offset(0, 2)), PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).Name = "=""Total Direct Cost"""
ActiveChart.SeriesCollection(2).Name = "=""Direct Cost Per
Trade"""
ActiveChart.Location Whe=xlLocationAsObject, Name:="coal
Charts"
With ActiveChart.Parent
.Left = 75
.Width = 375
.Top = 25
.Height = 275
End With

ActiveChart.ApplyCustomType ChartType:=xlBuiltIn,
TypeName:="Lines on 2
Axes"

etc etc