Problem with Legend.Position property in Excel 2007

[coolairmjj23]

All,

I have a 2003 vs. 2007 problem that I've spent a week or so trying to resolve, to no avail. I have created a fairly complex "dictator application" in Excel 2003. The application performs a bunch of calculations, and stores this inforamtion in a template workbook. The template itself contains one worksheet (to hold the data), and twelve empty "chart-sheets" which I later use to create plots. Of course, everything works fine and life is good in Excel 2003.

Flash forward two years, and half of my company is running 2007. When a 2007 user fires the plot creation routine, he is greated with a lovely error message (# 2147467259): "Method 'Position' of object 'Legend' failed".

Here's the full procedure I'm running to create the plots and position the legends:
<code>
Function bChartSumFileData(ByVal lTotalPlots As Long)

Const sSOURCE As String = "bChartSumFileData" 'Procedure Name (error-handling)

Dim bReturn As Boolean
Dim i As Long, j As Long, lNumSeries As Long
Dim sTemp As String, sPlotSetting As String

Dim chtTemp As Chart
Dim wksData As Worksheet, wksInfo As Worksheet
Dim serTemp As Series
Dim uSettings As CHART_SETTINGS
Dim vaPlotInfo As Variant

'Direct all subsequent errors to the error-handler
On Error GoTo ErrorHandler

'Assume success until an error is encountered
bReturn = True

'Refresh the reference to the results workbook
If Not bReferenceResultsWorkbook() Then Err.Raise glHANDLED_ERROR

'Remove any data currently stored on the Plot Data sheet
ClearPlotSeries

'Data will be written to and obtained from the results workbook, so we
'use a With statement for easier access to the workbook's objects
With gwbkResults
Set wksData = .Worksheets(1)
Set wksInfo = .Worksheets(2)

'Read the data from the "Plot Information" worksheet
vaPlotInfo = wksInfo.Range(wksInfo.Names("PlotInfo").RefersToRange.Address)

'Read the chart's settings from the plot settings file
sPlotSetting = CStr(GetSetting(gsREG_APPLICATION, gsREG_EXSECTION, "CurrentPlotSetting", ""))
If sPlotSetting = "" Then sPlotSetting = "Default"
If Not bReadChartSettings(sPlotSetting, uSettings) Then Err.Raise glHANDLED_ERROR

For i = 1 To lTotalPlots

'Obtain a reference to the current chart, and the number of series to plot.
Set chtTemp = .Charts(i)
lNumSeries = lGetNumChartSeries(i)
If lNumSeries = 0 Then GoTo NextCase

'Set the default chart type
chtTemp.ChartType = xlXYScatterLines

'Add all necessary series to the plot, taking their data from the "Plot Data" sheet
For j = 1 To lNumSeries
With chtTemp.SeriesCollection.NewSeries
.Name = CStr(wksData.Range(wksData.Names("Series" & _
i & "_" & j & "Name").RefersToRange.Address).Value)
.XValues = wksData.Range(wksData.Names("Data" & _
i & "Values").RefersToRange.Address)
.Values = wksData.Range(wksData.Names("Series" & _
i & "_" & j & "Values").RefersToRange.Address)
End With
Next j

'Set the main Chart Title for the plot
chtTemp.HasTitle = True
If Not bGetPlotTitle(uSettings, vaPlotInfo, sTemp, i, 0) Then Err.Raise glHANDLED_ERROR
chtTemp.ChartTitle.Text = sTemp

'Set the x- and y-axis titles for the plot
chtTemp.Axes(xlCategory).HasTitle = True
If Not bGetPlotTitle(uSettings, vaPlotInfo, sTemp, i, 1) Then Err.Raise glHANDLED_ERROR
chtTemp.Axes(xlCategory).AxisTitle.Text = sTemp
chtTemp.Axes(xlValue).HasTitle = True
If Not bGetPlotTitle(uSettings, vaPlotInfo, sTemp, i, 2) Then Err.Raise glHANDLED_ERROR
chtTemp.Axes(xlValue).AxisTitle.Text = sTemp

'Apply the font style formatting to the Chart Title, Axis Titles, Legend, etc
If Not bApplyFontFormatting(chtTemp, uSettings) Then Err.Raise glHANDLED_ERROR

'Apply axis label formatting (number format, gridlines, orientation, etc)
If Not bApplyAxisLabelFormatting(chtTemp, uSettings) Then Err.Raise glHANDLED_ERROR

'Apply the axis label scale properties (max, min, gridline units, etc).
If Not bApplyAxisScaleProperties(chtTemp, i, lNumSeries, _
uSettings) Then Err.Raise glHANDLED_ERROR

'Apply the series style formatting to each series in the plot
For Each serTemp In chtTemp.SeriesCollection
serTemp.ClearFormats
If Not bApplySeriesStyle(serTemp.PlotOrder, serTemp, uSettings) _
Then Err.Raise glHANDLED_ERROR
Next

'Apply the Legend's Placement property
If uSettings.lLegendPlacement <> xlNone Then
chtTemp.HasLegend = True
chtTemp.Legend.Position = uSettings.lLegendPlacement
Else
chtTemp.HasLegend = False
End If

'Apply formatting to the Chart Area, Plot Area and Legend Area
If Not bApplyAreaFormatting(chtTemp, uSettings) Then Err.Raise glHANDLED_ERROR

NextCase:
Next i

End With 'End of "With gwbkResults" statement

ErrorExit:

bChartSumFileData = bReturn
Exit Function

ErrorHandler:

bReturn = False
If bCentralErrorHandler(msMODULE, sSOURCE) Then
Stop
Resume
Else
Resume ErrorExit
End If

End Function
</code>

Obviously, lots of custom functions here; I'm also using a custom "type" to store chart settings (which I read from a *.txt file in a separate function). However, as far as the Legend goes, it shouldn't make a difference with the above code. When I stop execution after the error and look at the template workbook, the chart is created just fine, and the legend is visible, so I'm not sure why the error is occuring. Even better, the number of charts created prior to erroring-out is not fixed: sometimes the error occurs on the first plot, sometimes on the third, etc.

I know this is a lot of information, but I'm just wondering if someone has seen anything like this before? If anyone can help out, it would be greatly appreciated!

 

[coolairmjj23]

Bump.

 

[coolairmjj23]

Bump again...just to nip any confusion, here's the code that's generating an error:

<code>
'Apply the Legend's Placement property
If uSettings.lLegendPlacement <> xlNone Then
chtTemp.HasLegend = True
chtTemp.Legend.Position = uSettings.lLegendPlacement
Else
chtTemp.HasLegend = False
End If
</code>

Thanks!

 

[coolairmjj23]

Bump again...any help would be appreciated. Thanks!

 

[coolairmjj23]

Back on this problem after a few months of real work...

After a few hours of Google searching, I saw something regarding VBA.DoEvents. I tried using this code just prior to calling the Legend.Position property, but I'm still getting the error message.

As always, your advice, suggestions, or empathy is/are greatly appreciated. Thanks!

 

[coolairmjj23]

Bump...fingers crossed...

 

[Andrew Poulsom]

Maybe this will help you:

http://www.eggheadcafe.com/software/aspnet/33097972/excel-2007--legendposit.aspx

If you crosspost please mention it to save people's time:

http://www.ozgrid.com/forum/showthread.php?t=116062

 

[coolairmjj23]

I have tried inserting VBA.DoEvents prior to calling this code:

'Apply the Legend's Placement property
If uSettings.lLegendPlacement <> xlNone Then
    chtTemp.HasLegend = True
    VBA.DoEvents
    chtTemp.Legend.Position = uSettings.lLegendPlacement
Else
    chtTemp.HasLegend = False
End If

I still receive the same error message...any other thoughts?

 

[RoryA]

This might sound odd, but you could also try reading the property before you set it:

'Apply the Legend's Placement property
If uSettings.lLegendPlacement <> xlNone Then
chtTemp.HasLegend = True
doEvents
x = chtTemp.Legend.Position
chtTemp.Legend.Position = uSettings.lLegendPlacement
Else
chtTemp.HasLegend = False
End If

 

[coolairmjj23]

Thanks rorya and Andrew for your responses. Rorya, it sounds a little odd, but I've actually run into problems before where reading an object's property somehow helped Excel figure things out, behind the scenes...

...unfortunately, that's not the case this time. I've implemented your exact code in my application, to no avail. I appreciate your responses, though!

I'll gladly accept / try out any other proposed solutions...