run time 1004: the method 'applycustomtype' for object '_chart' fa
 

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

run time 1004: the method 'applycustomtype' for object '_chart' fa
 

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

run time 1004: the method 'applycustomtype' for object '_chart
 

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

run time 1004: the method 'applycustomtype' for object '_chart
 

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

run time 1004: the method 'applycustomtype' for object '_chart
 

Is it failing in the section of code that I modified or now that I made
changes is it failing in a section of the code that you didn't post?

You originally said it was failing, now you say it didn't run. Is it stil
failing. Reason why I'm asking is security level set correctly.

The problem looks like it being caused by a different version of excel
because it was failing on my PC. You could try to debug the problems on the
PC that is not working or send me the rest of the code to adjust.

You should get it working on all PC's just in case something happens to your
PC. The replacement PC may not work.

"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

run time 1004: the method 'applycustomtype' for object '_chart
 

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

run time 1004: the method 'applycustomtype' for object '_chart
 

Hi Joel,

I think it's something to do with the line that names the type of chart - my
colleague is in Germany and so the name of the chart is probably different??

Cheers,
Ewan.

"Joel" wrote:

Is it failing in the section of code that I modified or now that I made
changes is it failing in a section of the code that you didn't post?

You originally said it was failing, now you say it didn't run. Is it stil
failing. Reason why I'm asking is security level set correctly.

The problem looks like it being caused by a different version of excel
because it was failing on my PC. You could try to debug the problems on the
PC that is not working or send me the rest of the code to adjust.

You should get it working on all PC's just in case something happens to your
PC. The replacement PC may not work.

"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

run time 1004: the method 'applycustomtype' for object '_chart
 

If he doesn't mind having the chart names on his machine in English
instead of German, you could just copy a version of the English
"Xl8garly.xls" file over his German version...

The English chart gallery file should be located at something like:
C:\Program Files\Microsoft Office\Office10\1033
The German version at:
C:\Program Files\Microsoft Office\Office10\1031
(depending on the office version and installation path of course).

Or he just opens up his German chart gallery file and renames the chart
type concerned so that it matches the English name (check the sheet
names in Xl8galry.xls).

Or you do the VBA approach:
If Application.Language = 1033 then
blablabla
else
bliblibli
endif



ewan7279 wrote:
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

run time 1004: the method 'applycustomtype' for object '_chart
 

Cheers Luca, I'll try both...

"Luca Brasi" wrote:

If he doesn't mind having the chart names on his machine in English
instead of German, you could just copy a version of the English
"Xl8garly.xls" file over his German version...

The English chart gallery file should be located at something like:
C:\Program Files\Microsoft Office\Office10\1033
The German version at:
C:\Program Files\Microsoft Office\Office10\1031
(depending on the office version and installation path of course).

Or he just opens up his German chart gallery file and renames the chart
type concerned so that it matches the English name (check the sheet
names in Xl8galry.xls).

Or you do the VBA approach:
If Application.Language = 1033 then
blablabla
else
bliblibli
endif



ewan7279 wrote:
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

run time 1004: the method 'applycustomtype' for object '_chart
 

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

run time 1004: the method 'applycustomtype' for object '_chart
 

This came up in another thread -
The German equivalent for "Line - Column on 2 Axes" is "Linie - Säule auf
zwei Achsen".

I haven't tried this but you might be able to do

if Application.International(xlCountrySetting) = 49
then ' German string assuming 49 is right?

In the same thread I tried to devise a routine that would apply the correct
custom type for use in any language system, though it didn't seem to work
for the OP in that thread!

However, best to go with Jon's suggestion and build the custom type
yourself.

Regards,
Peter T


"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

run time 1004: the method 'applycustomtype' for object '_chart
 

if Application.International(xlCountrySetting) = 49
then ' German string assuming 49 is right?

The reason my approach will be better, is that your German user will forward
it to a Dutch user, and he will share it with his French girlfriend, and
before you know it, you'll eventually come across a language you aren't
prepared for.

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


"Peter T" <peter_t@discussions wrote in message
...
This came up in another thread -
The German equivalent for "Line - Column on 2 Axes" is "Linie - Säule auf
zwei Achsen".

I haven't tried this but you might be able to do

if Application.International(xlCountrySetting) = 49
then ' German string assuming 49 is right?

In the same thread I tried to devise a routine that would apply the
correct
custom type for use in any language system, though it didn't seem to work
for the OP in that thread!

However, best to go with Jon's suggestion and build the custom type
yourself.

Regards,
Peter T


"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

run time 1004: the method 'applycustomtype' for object '_chart
 

Thanks all. I'll try these out and come back to you.

"Jon Peltier" wrote:

if Application.International(xlCountrySetting) = 49
then ' German string assuming 49 is right?

The reason my approach will be better, is that your German user will forward
it to a Dutch user, and he will share it with his French girlfriend, and
before you know it, you'll eventually come across a language you aren't
prepared for.

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


"Peter T" <peter_t@discussions wrote in message
...
This came up in another thread -
The German equivalent for "Line - Column on 2 Axes" is "Linie - Säule auf
zwei Achsen".

I haven't tried this but you might be able to do

if Application.International(xlCountrySetting) = 49
then ' German string assuming 49 is right?

In the same thread I tried to devise a routine that would apply the
correct
custom type for use in any language system, though it didn't seem to work
for the OP in that thread!

However, best to go with Jon's suggestion and build the custom type
yourself.

Regards,
Peter T


"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

run time 1004: the method 'applycustomtype' for object '_chart
 

Hi Joel,

The code was working on Friday, but now does not fails at line 'Set newchart
= Charts.Add' with the error 'Compile error: Expected Function or Variable'.
Any idea why?

"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

run time 1004: the method 'applycustomtype' for object '_chart
 

Hi Jon,

I'm afraid I can't work out how to do what you mentioned. I get a runtime
error 1004 message "Method 'Axes' of object '_Chart' failed".

How do I 'specifically change the axis group of the desired series to
xlSecondary.'? Could you show me with this code please?:

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

End With

With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Financials"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Total Direct
Cost"
.Axes(xlCategory, xlSecondary).HasTitle = False '<== This causes
the error
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Direct Cost
Per Trade"
End With
End Sub


Thanks,
Ewan.


"ewan7279" wrote:

Thanks all. I'll try these out and come back to you.

"Jon Peltier" wrote:

if Application.International(xlCountrySetting) = 49
then ' German string assuming 49 is right?

The reason my approach will be better, is that your German user will forward
it to a Dutch user, and he will share it with his French girlfriend, and
before you know it, you'll eventually come across a language you aren't
prepared for.

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


"Peter T" <peter_t@discussions wrote in message
...
This came up in another thread -
The German equivalent for "Line - Column on 2 Axes" is "Linie - Säule auf
zwei Achsen".

I haven't tried this but you might be able to do

if Application.International(xlCountrySetting) = 49
then ' German string assuming 49 is right?

In the same thread I tried to devise a routine that would apply the
correct
custom type for use in any language system, though it didn't seem to work
for the OP in that thread!

However, best to go with Jon's suggestion and build the custom type
yourself.

Regards,
Peter T


"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

run time 1004: the method 'applycustomtype' for object '_chart
 

To switch a series:

ActiveChart.SeriesCollection(2).AxisGroup = xlSecondary

Then just to make sure the axes appear:

ActiveChart.HasAxis(xlCategory, xlSecondary) = True
ActiveChart.HasAxis(xlValue, xlSecondary) = True

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


"ewan7279" wrote in message
...
Hi Jon,

I'm afraid I can't work out how to do what you mentioned. I get a runtime
error 1004 message "Method 'Axes' of object '_Chart' failed".

How do I 'specifically change the axis group of the desired series to
xlSecondary.'? Could you show me with this code please?:

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

End With

With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Financials"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Total Direct
Cost"
.Axes(xlCategory, xlSecondary).HasTitle = False '<== This causes
the error
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Direct
Cost
Per Trade"
End With
End Sub


Thanks,
Ewan.


"ewan7279" wrote:

Thanks all. I'll try these out and come back to you.

"Jon Peltier" wrote:

if Application.International(xlCountrySetting) = 49
then ' German string assuming 49 is right?

The reason my approach will be better, is that your German user will
forward
it to a Dutch user, and he will share it with his French girlfriend,
and
before you know it, you'll eventually come across a language you aren't
prepared for.

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


"Peter T" <peter_t@discussions wrote in message
...
This came up in another thread -
The German equivalent for "Line - Column on 2 Axes" is "Linie - Säule
auf
zwei Achsen".

I haven't tried this but you might be able to do

if Application.International(xlCountrySetting) = 49
then ' German string assuming 49 is right?

In the same thread I tried to devise a routine that would apply the
correct
custom type for use in any language system, though it didn't seem to
work
for the OP in that thread!

However, best to go with Jon's suggestion and build the custom type
yourself.

Regards,
Peter T


"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

run time 1004: the method 'applycustomtype' for object '_chart
 

Hi Jon,

Thanks for this. I included this code into my macro, but unfortunately it
is now a bar chart rather than a line chart. What can I do to convert it
back to a line chart?

Thanks,
Ewan.

"Jon Peltier" wrote:

To switch a series:

ActiveChart.SeriesCollection(2).AxisGroup = xlSecondary

Then just to make sure the axes appear:

ActiveChart.HasAxis(xlCategory, xlSecondary) = True
ActiveChart.HasAxis(xlValue, xlSecondary) = True

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


"ewan7279" wrote in message
...
Hi Jon,

I'm afraid I can't work out how to do what you mentioned. I get a runtime
error 1004 message "Method 'Axes' of object '_Chart' failed".

How do I 'specifically change the axis group of the desired series to
xlSecondary.'? Could you show me with this code please?:

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

End With

With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Financials"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Total Direct
Cost"
.Axes(xlCategory, xlSecondary).HasTitle = False '<== This causes
the error
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Direct
Cost
Per Trade"
End With
End Sub


Thanks,
Ewan.


"ewan7279" wrote:

Thanks all. I'll try these out and come back to you.

"Jon Peltier" wrote:

if Application.International(xlCountrySetting) = 49
then ' German string assuming 49 is right?

The reason my approach will be better, is that your German user will
forward
it to a Dutch user, and he will share it with his French girlfriend,
and
before you know it, you'll eventually come across a language you aren't
prepared for.

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


"Peter T" <peter_t@discussions wrote in message
...
This came up in another thread -
The German equivalent for "Line - Column on 2 Axes" is "Linie - Säule
auf
zwei Achsen".

I haven't tried this but you might be able to do

if Application.International(xlCountrySetting) = 49
then ' German string assuming 49 is right?

In the same thread I tried to devise a routine that would apply the
correct
custom type for use in any language system, though it didn't seem to
work
for the OP in that thread!

However, best to go with Jon's suggestion and build the custom type
yourself.

Regards,
Peter T


"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")

run time 1004: the method 'applycustomtype' for object '_chart
 

ActiveChart.ChartType = xlLine

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


"ewan7279" wrote in message
...
Hi Jon,

Thanks for this. I included this code into my macro, but unfortunately
it
is now a bar chart rather than a line chart. What can I do to convert it
back to a line chart?

Thanks,
Ewan.

"Jon Peltier" wrote:

To switch a series:

ActiveChart.SeriesCollection(2).AxisGroup = xlSecondary

Then just to make sure the axes appear:

ActiveChart.HasAxis(xlCategory, xlSecondary) = True
ActiveChart.HasAxis(xlValue, xlSecondary) = True

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


"ewan7279" wrote in message
...
Hi Jon,

I'm afraid I can't work out how to do what you mentioned. I get a
runtime
error 1004 message "Method 'Axes' of object '_Chart' failed".

How do I 'specifically change the axis group of the desired series to
xlSecondary.'? Could you show me with this code please?:

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

End With

With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Financials"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Total
Direct
Cost"
.Axes(xlCategory, xlSecondary).HasTitle = False '<== This
causes
the error
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Direct
Cost
Per Trade"
End With
End Sub


Thanks,
Ewan.


"ewan7279" wrote:

Thanks all. I'll try these out and come back to you.

"Jon Peltier" wrote:

if Application.International(xlCountrySetting) = 49
then ' German string assuming 49 is right?

The reason my approach will be better, is that your German user will
forward
it to a Dutch user, and he will share it with his French girlfriend,
and
before you know it, you'll eventually come across a language you
aren't
prepared for.

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


"Peter T" <peter_t@discussions wrote in message
...
This came up in another thread -
The German equivalent for "Line - Column on 2 Axes" is "Linie -
Säule
auf
zwei Achsen".

I haven't tried this but you might be able to do

if Application.International(xlCountrySetting) = 49
then ' German string assuming 49 is right?

In the same thread I tried to devise a routine that would apply
the
correct
custom type for use in any language system, though it didn't seem
to
work
for the OP in that thread!

However, best to go with Jon's suggestion and build the custom
type
yourself.

Regards,
Peter T


"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")

run time 1004: the method 'applycustomtype' for object '_chart
 

Hi Jon,

I tried this and it works, but does this not bring me back to the problem of
referring to the chart name in English again or is it only built-in custom
types that have different names in different languages?

Thanks,
Ewan.

"Jon Peltier" wrote:

ActiveChart.ChartType = xlLine

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


"ewan7279" wrote in message
...
Hi Jon,

Thanks for this. I included this code into my macro, but unfortunately
it
is now a bar chart rather than a line chart. What can I do to convert it
back to a line chart?

Thanks,
Ewan.

"Jon Peltier" wrote:

To switch a series:

ActiveChart.SeriesCollection(2).AxisGroup = xlSecondary

Then just to make sure the axes appear:

ActiveChart.HasAxis(xlCategory, xlSecondary) = True
ActiveChart.HasAxis(xlValue, xlSecondary) = True

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


"ewan7279" wrote in message
...
Hi Jon,

I'm afraid I can't work out how to do what you mentioned. I get a
runtime
error 1004 message "Method 'Axes' of object '_Chart' failed".

How do I 'specifically change the axis group of the desired series to
xlSecondary.'? Could you show me with this code please?:

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

End With

With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Financials"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Total
Direct
Cost"
.Axes(xlCategory, xlSecondary).HasTitle = False '<== This
causes
the error
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Direct
Cost
Per Trade"
End With
End Sub


Thanks,
Ewan.


"ewan7279" wrote:

Thanks all. I'll try these out and come back to you.

"Jon Peltier" wrote:

if Application.International(xlCountrySetting) = 49
then ' German string assuming 49 is right?

The reason my approach will be better, is that your German user will
forward
it to a Dutch user, and he will share it with his French girlfriend,
and
before you know it, you'll eventually come across a language you
aren't
prepared for.

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


"Peter T" <peter_t@discussions wrote in message
...
This came up in another thread -
The German equivalent for "Line - Column on 2 Axes" is "Linie -
Säule
auf
zwei Achsen".

I haven't tried this but you might be able to do

if Application.International(xlCountrySetting) = 49
then ' German string assuming 49 is right?

In the same thread I tried to devise a routine that would apply
the
correct
custom type for use in any language system, though it didn't seem
to
work
for the OP in that thread!

However, best to go with Jon's suggestion and build the custom
type
yourself.

Regards,
Peter T


"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

run time 1004: the method 'applycustomtype' for object '_chart' fa
 

I added the following code to overcome the problem:

If Application.LanguageSettings.LanguageID(msoLanguag eIDInstall) = 2057 Then
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Lines
on 2 Axes"
Else
If Application.LanguageSettings.LanguageID(msoLanguag eIDInstall) =
1031 Then
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn,
TypeName:="Linien auf zwei Achsen"
Else
MsgBox "Language not supported"
End If
End If

"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

run time 1004: the method 'applycustomtype' for object '_chart' fa
 

FWIW my LanguageID is 1033 (even though Eng/Eng), perhaps something like
this -

Select case Application.LanguageSettings.LanguageID(msoLanguag eIDInstall
Case 1033, 2057 ' lots more English codes
sTypeName = "Lines on 2 Axes" case 1031, 3079, 5127, 4103, 2055
sTypeName = "Linien auf zwei Achsen"
Case else: Msgbox "Language not supported"
End Select

If Len(sTypeName) = 0 Then
' Exit Sub ?
Else ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, _
TypeName:= sTypeName
End if

If you're only exchanging between one or two sites and the above always
works - fine. But keep in mind what Jon mentioned about multiple languages.
There's also another point he alluded to, it's generally safer to create
custom charts yourself, irrespective of language issues, rather than rely on
built in chart file always being available.

Regards,
Peter T

PS, just for my curiosity, I would be interested to know if the macro I
suggested will work for multiple languages, (subject the gallery file being
available)
http://tinyurl.com/yuwjxv

See post 5 in date view and please amend the typo (see follow-up post) so
that the test builtin typename is in English (not German as in the sub
Test), eg
sTypeName = "Line - Column on 2 Axes"



"ewan7279" wrote in message
...
I added the following code to overcome the problem:

If Application.LanguageSettings.LanguageID(msoLanguag eIDInstall) = 2057
Then
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Lines
on 2 Axes"
Else
If Application.LanguageSettings.LanguageID(msoLanguag eIDInstall) =
1031 Then
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn,
TypeName:="Linien auf zwei Achsen"
Else
MsgBox "Language not supported"
End If
End If

"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

run time 1004: the method 'applycustomtype' for object '_chart' fa
 

A line feed went missing !

Select case Application.LanguageSettings.LanguageID(msoLanguag eIDInstall
Case 1033, 2057 ' lots more English codes
sTypeName = "Lines on 2 Axes" case 1031, 3079, 5127, 4103, 2055
sTypeName = "Linien auf zwei Achsen"
Case else: Msgbox "Language not supported"
End Select


Case 1033, 2057 ' lots more English codes
sTypeName = "Lines on 2 Axes"
Case 1031, 3079, 5127, 4103, 2055
sTypeName = "Linien auf zwei Achsen"

Peter T

run time 1004: the method 'applycustomtype' for object '_chart
 

Hi Peter,

The solution works fine for now, but I will bear your suggestion in mind in
case it goes wrong...

Jon's suggestion to build the charts myself seems like a good idea, but the
solution only gave me a bar chart. To convert this to a line chart, Jon
suggested I use ActiveChart.ChartType = xlLine, but does this not prompt the
same problem by referring to a built-in chart type in English?

Cheers,
Ewan.

"Peter T" wrote:

FWIW my LanguageID is 1033 (even though Eng/Eng), perhaps something like
this -

Select case Application.LanguageSettings.LanguageID(msoLanguag eIDInstall
Case 1033, 2057 ' lots more English codes
sTypeName = "Lines on 2 Axes" case 1031, 3079, 5127, 4103, 2055
sTypeName = "Linien auf zwei Achsen"
Case else: Msgbox "Language not supported"
End Select

If Len(sTypeName) = 0 Then
' Exit Sub ?
Else ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, _
TypeName:= sTypeName
End if

If you're only exchanging between one or two sites and the above always
works - fine. But keep in mind what Jon mentioned about multiple languages.
There's also another point he alluded to, it's generally safer to create
custom charts yourself, irrespective of language issues, rather than rely on
built in chart file always being available.

Regards,
Peter T

PS, just for my curiosity, I would be interested to know if the macro I
suggested will work for multiple languages, (subject the gallery file being
available)
http://tinyurl.com/yuwjxv

See post 5 in date view and please amend the typo (see follow-up post) so
that the test builtin typename is in English (not German as in the sub
Test), eg
sTypeName = "Line - Column on 2 Axes"



"ewan7279" wrote in message
...
I added the following code to overcome the problem:

If Application.LanguageSettings.LanguageID(msoLanguag eIDInstall) = 2057
Then
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Lines
on 2 Axes"
Else
If Application.LanguageSettings.LanguageID(msoLanguag eIDInstall) =
1031 Then
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn,
TypeName:="Linien auf zwei Achsen"
Else
MsgBox "Language not supported"
End If
End If

"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

run time 1004: the method 'applycustomtype' for object '_chart
 

Hi Ewan -

Jon's suggestion to build the charts myself seems like a good idea, but
the
solution only gave me a bar chart. To convert this to a line chart, Jon
suggested I use ActiveChart.ChartType = xlLine, but does this not prompt
the
same problem by referring to a built-in chart type in English?

I don't follow, that code is same as manually right-click on the chart area
ChartType etc, (can also apply different ChartType's to individual series)
and I don't think in any way related to language issues. Describe what the
problem is.

Regards,
Peter T

run time 1004: the method 'applycustomtype' for object '_chart
 

Hi Peter,

I have just altered the code again (as I had before) as such:

ActiveChart.ChartType = xlLineMarkers

'format and position chart
With ActiveChart
.SeriesCollection(2).AxisGroup = xlSecondary
.HasAxis(xlCategory, xlSecondary) = False
.HasAxis(xlValue, xlSecondary) = True
.HasTitle = True
.ChartTitle.Characters.Text = "Financials"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Total Direct Cost"
.Axes(xlCategory, xlSecondary).HasTitle = False
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Direct Cost Per
Trade"
End With

This now works for the user, so i can only assume there must have been an
EBCAK (error between chair and keyboard).

Thanks for your input,
Ewan.

"Peter T" wrote:

Hi Ewan -

Jon's suggestion to build the charts myself seems like a good idea, but
the
solution only gave me a bar chart. To convert this to a line chart, Jon
suggested I use ActiveChart.ChartType = xlLine, but does this not prompt
the
same problem by referring to a built-in chart type in English?

I don't follow, that code is same as manually right-click on the chart area
ChartType etc, (can also apply different ChartType's to individual series)
and I don't think in any way related to language issues. Describe what the
problem is.

Regards,
Peter T

run time 1004: the method 'applycustomtype' for object '_chart
 

The built-in custom types have text names, but the standard types use
invariant constants to indicate type.

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


"ewan7279" wrote in message
...
Hi Jon,

I tried this and it works, but does this not bring me back to the problem
of
referring to the chart name in English again or is it only built-in custom
types that have different names in different languages?

Thanks,
Ewan.

"Jon Peltier" wrote:

ActiveChart.ChartType = xlLine

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


"ewan7279" wrote in message
...
Hi Jon,

Thanks for this. I included this code into my macro, but
unfortunately
it
is now a bar chart rather than a line chart. What can I do to convert
it
back to a line chart?

Thanks,
Ewan.

"Jon Peltier" wrote:

To switch a series:

ActiveChart.SeriesCollection(2).AxisGroup = xlSecondary

Then just to make sure the axes appear:

ActiveChart.HasAxis(xlCategory, xlSecondary) = True
ActiveChart.HasAxis(xlValue, xlSecondary) = True

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


"ewan7279" wrote in message
...
Hi Jon,

I'm afraid I can't work out how to do what you mentioned. I get a
runtime
error 1004 message "Method 'Axes' of object '_Chart' failed".

How do I 'specifically change the axis group of the desired series
to
xlSecondary.'? Could you show me with this code please?:

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

End With

With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Financials"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
"Month"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Total
Direct
Cost"
.Axes(xlCategory, xlSecondary).HasTitle = False '<== This
causes
the error
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =
"Direct
Cost
Per Trade"
End With
End Sub


Thanks,
Ewan.


"ewan7279" wrote:

Thanks all. I'll try these out and come back to you.

"Jon Peltier" wrote:

if Application.International(xlCountrySetting) = 49
then ' German string assuming 49 is right?

The reason my approach will be better, is that your German user
will
forward
it to a Dutch user, and he will share it with his French
girlfriend,
and
before you know it, you'll eventually come across a language you
aren't
prepared for.

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


"Peter T" <peter_t@discussions wrote in message
...
This came up in another thread -
The German equivalent for "Line - Column on 2 Axes" is "Linie -
Säule
auf
zwei Achsen".

I haven't tried this but you might be able to do

if Application.International(xlCountrySetting) = 49
then ' German string assuming 49 is right?

In the same thread I tried to devise a routine that would apply
the
correct
custom type for use in any language system, though it didn't
seem
to
work
for the OP in that thread!

However, best to go with Jon's suggestion and build the custom
type
yourself.

Regards,
Peter T


"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

run time 1004: the method 'applycustomtype' for object '_chart
 

xlLine is a numerical constant (value = 4) which has a name that looks like
text ("xlLine"). Chart type 4 is the same in all localizations of Excel.

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


"ewan7279" wrote in message
...
Hi Peter,

The solution works fine for now, but I will bear your suggestion in mind
in
case it goes wrong...

Jon's suggestion to build the charts myself seems like a good idea, but
the
solution only gave me a bar chart. To convert this to a line chart, Jon
suggested I use ActiveChart.ChartType = xlLine, but does this not prompt
the
same problem by referring to a built-in chart type in English?

Cheers,
Ewan.

"Peter T" wrote:

FWIW my LanguageID is 1033 (even though Eng/Eng), perhaps something like
this -

Select case Application.LanguageSettings.LanguageID(msoLanguag eIDInstall
Case 1033, 2057 ' lots more English codes
sTypeName = "Lines on 2 Axes" case 1031, 3079, 5127, 4103, 2055
sTypeName = "Linien auf zwei Achsen"
Case else: Msgbox "Language not supported"
End Select

If Len(sTypeName) = 0 Then
' Exit Sub ?
Else ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, _
TypeName:= sTypeName
End if

If you're only exchanging between one or two sites and the above always
works - fine. But keep in mind what Jon mentioned about multiple
languages.
There's also another point he alluded to, it's generally safer to create
custom charts yourself, irrespective of language issues, rather than rely
on
built in chart file always being available.

Regards,
Peter T

PS, just for my curiosity, I would be interested to know if the macro I
suggested will work for multiple languages, (subject the gallery file
being
available)
http://tinyurl.com/yuwjxv

See post 5 in date view and please amend the typo (see follow-up post) so
that the test builtin typename is in English (not German as in the sub
Test), eg
sTypeName = "Line - Column on 2 Axes"



"ewan7279" wrote in message
...
I added the following code to overcome the problem:

If Application.LanguageSettings.LanguageID(msoLanguag eIDInstall) = 2057
Then
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn,
TypeName:="Lines
on 2 Axes"
Else
If
Application.LanguageSettings.LanguageID(msoLanguag eIDInstall) =
1031 Then
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn,
TypeName:="Linien auf zwei Achsen"
Else
MsgBox "Language not supported"
End If
End If

"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