Thread: Chart.Labels.Add function doesn't work in excel 2007

Hi

I am new to macros. We are using some macros which is running perfectly fine in excel 2003 but in 2007 it is throwing error in following function. So can you please help on this.

The error comes at line
Set objLabe2 = cht.Labels.Add(dblXPosition, dblYPosition, intLength, intHeight)

in following function (this line don't create any error if excel 2003 installed on my machine but if excel 2007 installed then it says unknown exception occured in the system.

Code:

Sub CreateLabelAtPositionOnGraph(dblXValue As Double, dblYValue As Double, strText As String, Optional intHeight As Integer = STD_LABEL_HEIGHT, Optional intLength As Integer = STD_LABEL_WIDTH, Optional intNudgeUp As Integer = 0, Optional intNudgeDown As Integer = 0, Optional intNudgeLeft As Integer = 0)

    'Creates a label on the active chart at a poisiton ref given in terms of the chart
    'e.g. if the x axis is a date the paramter will be sent as ( e.g. cdbl(cdate("30 April 2004"))
    
    'In reality the label goes about 8 points above the graph position

    Dim cht As Chart
    Dim objLabel As Object

    Dim Xleft As Double
    Dim Ytop As Double
    Dim Xwidth As Double
    Dim Yheight As Double
    Dim dblXPercentage  As Double
    Dim dblYPercentage As Double
    Dim dblXPosition As Double
    Dim dblYPosition As Double
    
    
    Set cht = ActiveChart
    
    ActiveSheet.ChartObjects("cht").Activate
    Xleft = cht.PlotArea.InsideLeft
    Xwidth = cht.PlotArea.InsideWidth
    
    Ytop = cht.PlotArea.InsideTop
    Yheight = cht.PlotArea.InsideHeight

    
    dblXPercentage = (dblXValue - mdblXmin) / (mdblXmax - mdblXmin)
    dblYPercentage = (dblYValue - mdblYmin) / (mdblYmax - mdblYmin)
    
    dblXPosition = Xleft + Xwidth * dblXPercentage
    dblXPosition = dblXPosition + intNudgeLeft
    
    dblYPosition = Yheight * (1 - dblYPercentage)  '1- because it goes from the top
    dblYPosition = dblYPosition - intNudgeUp + intNudgeDown
    Set objLabe2 = cht.Labels.Add(dblXPosition, dblYPosition, intLength, intHeight)
    objLabel.Characters.Text = strText

End Sub

Try changing ...

Code:

Set objLabe2 = cht.Labels.Add(dblXPosition, dblYPosition, intLength, intHeight)

to

Code:

Set objLabel = cht.Labels.Add(dblXPosition, dblYPosition, intLength, intHeight)

objLabe2 is never declared anywhere, but objLabel is. And, the next line uses objLabel, not objLabe2.

There seems to be other potential issues. Like ...
* mdblXmin, mdblXmax and mdblYmin, mdblYmax never get defined (???), and ...
* ActiveSheet.ChartObjects("cht").Activate ... failed for me. I commented it out; and it worked. I don't think you need it anyway.
... Regards, sauerj

Hi

Thanks for reply. I am sorry mistakenly I have typed 2 there but it is objLable only and in Debug mode I am getting all the values withoot any problem. Moreoverly it is working without any problem if Excel 2003 installed machines. But now all our machines are installed with Excel 2007. Since then it is theowing error at this place.

Thanks
Krishna

Krishna, when you are replying to a post directly above, please do not quote. It just creates clutter. Thanks.

In xl2007 you will need code like this

Code:

    Dim objLabel As Shape
    
    Set objLabel = ActiveChart.Shapes.AddLabel(msoTextOrientationHorizontal, 10, 10, 25, 20)
    objLabel.TextFrame.Characters.Text = "StrText"