Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Defining a Chart Object in Class Module (Excel 2003)

    Hi,

    I am starting to create a class module called "Graph" to see if it can help me in creating and manipulating many charts in an Excel workbook.

    The following code creates a chart and places it on "Sheet2" as required, but it refuses to allow me to enable or set the title.

    The error message is : "Method 'HasTitle' of object '_Chart' failed" Now I am not sure why the error msg refers to a "Method", because "HasTitle" is a "Property" of a Chart object. I'm missing something here.

    The Class Module code is :

    '================================================= ==========
    Option Explicit

    Private strTitle As String
    Private cht As Chart

    Sub Create(strShtName As String)

    On Error GoTo ErrorHandler

    ActiveWorkbook.Charts.Add
    Set cht = ActiveChart
    cht.Location Where:=xlLocationAsObject, Name:=strShtName
    cht.ChartType = xlXYScatterLines
    cht.HasTitle = True 'code fails on this line

    ExitHandler:

    Exit Sub

    ErrorHandler:

    MsgBox "Error Number : " & Err.Number & "; Description : " & Err.Description, vbCritical, "Error . . ."
    Resume ExitHandler

    End Sub

    Property Get Title() As String
    Title = strTitle
    End Property

    Property Let Title(ByVal str As String)
    strTitle = str
    cht.ChartTitle.Text = strTitle 'this line won't execute
    End Property
    '================================================= ===========



    The code to create the chart is :

    '================================================= ===========
    Sub TestGraphClassAdd()

    Dim grf As Graph
    Dim strTitle As String

    Set grf = New Graph

    grf.Create "Sheet2"

    grf.Title = "Test Title"

    End Sub
    '===========================

    Any help greatly appreciated as I haven't any experience with class modules.

    Thanks,

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Defining a Chart Object in Class Module (Excel 2003)

    Please excuse my negligence here. I may be wrong with my advice.

    I recall that when working with charts there are two objects you can refer to. The Shape object class and the ChartObject object class. If you are going to want to move, or size, etc.. the chart, you can use the Shape object or the ChartObject object class to refer to the chart, but if you want to work with anything INSIDE the chart you must refer to it using the ChartObject object class.

    So, make sure that you have (Dim)-med your variables correctly to get a reference to the properties inside the chart! Then you will be able to work with them!
    Regards,
    Rudi

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Defining a Chart Object in Class Module (Excel 2003)

    You can't set the title properties of an empty chart. You'll have to set the source data before setting the title, or even the HasTitle property.

    Added: you have to repeat the line

    Set cht = ActiveChart

    after changing the location of the chart from a chart sheet to the specified worksheet.

  4. #4
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Defining a Chart Object in Class Module (Excel 2003)

    Hans,

    Thanks a lot, that worked fine.

    Now I can't get the property to set the Title <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    The class module code for the create method is now :

    Private strTitle As String
    Private cht As Chart

    Sub Create(strShtName As String, strDataRangeName As String)
    . . .
    ActiveWorkbook.Charts.Add
    Set cht = ActiveChart
    cht.ChartType = xlXYScatterLines
    cht.Location Where:=xlLocationAsObject, Name:=strShtName
    cht.SetSourceData Range(strDataRangeName)
    cht.HasTitle = True
    . . .
    End Sub

    But, the following now gives an error (Automation Error) :

    Property Let Title(ByVal str As String)
    strTitle = str
    cht.ChartTitle.Text = strTitle
    End Property

    The calling procedure is :

    Sub TestGraphClassAdd()

    Dim grf As Graph
    Dim strTitle As String

    Set grf = New Graph

    grf.Create "Sheet2", "GraphData" 'create the chart on Sheet2 and use the named range to provide data

    grf.Title = "Test Title"

    End Sub

    Does it make sense to use a class module to do what I'm doing or should I rather make a set of "normal" functions and procedures to do this ?

    Thanks a lot for your help,

    Regards,

  5. #5
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Defining a Chart Object in Class Module (Excel 2003)

    Thanks, set cht = ActiveChart did the trick in the property function as well.

  6. #6
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Defining a Chart Object in Class Module (Excel 2003)

    Rudi,

    Thanks - I still have the fun part of moving, sizing, aligning, formatting a whole range of charts coming up. I had seen the Shape object used to move charts around,

    Regards,

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Defining a Chart Object in Class Module (Excel 2003)

    Did you read the remark I added afterwards to my reply? You have to set the cht variable again after moving the chart to the worksheet. The code then runs OK for me.

    A class module will work here, but so would a set of functions in a standard module. You'd use a CreateGraph function that returns a chart or chartobject.

  8. #8
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Defining a Chart Object in Class Module (Excel 2003)

    Thanks, I saw your addendum to the original reply . . .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •