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

[Guest]

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 Where:=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

 

[Guest]

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 Where:=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

 

[Guest]

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.

 

[Luca Brasi]

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

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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.

 

[Luca Brasi]

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

 

[Guest]

Cheers Luca, I'll try both...

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

 

[Jon Peltier]

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

 

[Peter T]

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

 

[Jon Peltier]

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

 

[Guest]

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

 

[Guest]

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?

 

[Guest]

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 Where:=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.

 

[Jon Peltier]

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

 

[Guest]

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]

ActiveChart.ChartType = xlLine

- Jon

 

[Guest]

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.

 

[Guest]

I added the following code to overcome the problem:

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