Setting Excel Chart type

[donjohnson]

Hello!

I am a relative newbie to VB, and am having a problem with a script setting a chart type for EXCEL. I can set a type of any of a handful (mostly 3-d chart types), but if I set anything else, I get a run time 1004, Unable to set the Type property of the Chart class.

What am I doing wrong here?

Here is the piece of code that fails:

    'Add a chart sheet (based on the chart range)
    'Assign the new chart to a variable.
    Set ExcelChart = ExcelSheet.Application.Charts.Add
    With ExcelChart
[COLOR=red]
        .Type = chartType
[/color]
        .HasTitle = True
        .ChartTitle.Characters.Text = "My Chart Title"
    End With

chartType is Dim'ed as Integer (I also tried Long, with same results).

I have tried to use the defined constants in VB, such as XLBarStacked, XL3DBarStacked; I have tried assigning the direct values (58 and 59), but still most of the XLChartType constants do not work.

The rest of the code works fine, and the chart is produced fine when the chart type is valid, so this is my focus.

Please email me directly as well as replying here, to help me as quickly as possible.

Thanks!

Don

 

[SkipVought]

Hi,

Check out in HELP ChartType Property. Theres a whole list to xl constants.


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[donjohnson]

Thanks, Skip; I have looked up the various constants, and they are coded correctly, but for some reason the script fails. For example, I can use xlBar and xl3dBar, but when I use xlBarStacked or xl3dbarStacked, these give the 1004.

Im stumped!

Don

 

[SkipVought]

are you using ChartType instead of Type???

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[donjohnson]

Well, there's much egg on my face! You are right, and I have since been able to even create a custom style chart beyond my first project.

Thanks for the second pair of eyes!

Don