Method 'select' of object '_chart' failed

[deo89]

I have a user that is getting this error on his Excel spreadsheet when attempting to use a drop down box selection.


Run-time Error -2147417848
Method 'select' of object '_chart' failed



- This same macro works on a previous version of the spreadsheet
- He recently updated the data page with new numbers, then saved w/ new name
- Office 2000 and 2003, WinXP sp1 and sp2
- Excel crashes after posting the error.
- Debug highlights the last line of the dropdown1 subroutine

I've found information regarding early vs. late binding, but since I'm not a developer of any sort (that person is out of the office), I'm not sure how to apply it to this code:




Public PlantSpaceWorkbook As Object
Public rngRawData As Range

Sub DropDown1_Change()
Set PlantSpaceWorkbook = ActiveWorkbook
Windows(PlantSpaceWorkbook.Name).Activate

CopyRawData

Sheets("ChartData").Select
Range("Data").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("ChartCriteria"), Unique:=False
Sheets("Report").Select
End Sub
'
'
'
'
'
Sub RefreshFieldList()
Worksheets("Raw Data Entry").Select
Range("B2", Range("B2").End(xlDown)).Select
Set rngListSource = Selection
ActiveWorkbook.Names.Add Name:="ListSource", RefersTo:=Selection

Sheets("ListData").Select
Range("A2").Select

Range("ListSource").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range _
("A1"), Unique:=True
Range("A1").ClearContents
Sheets("Report").Select
End Sub

'
'
'
'
'
Sub CopyRawData()
'
' CopyRawData Macro
' Macro recorded 3/14/2003 by Sara E. McDowell
'

Set PlantSpaceWorkbook = ActiveWorkbook

'Update the ChartData sheet.
Worksheets("ChartData").Select


'Show hidden rows; select current region

If ActiveSheet.FilterMode = True Then
Worksheets("ChartData").ShowAllData
End If

Range("A5").Select
Set rngChartData = Selection.CurrentRegion

'If the range is more than one row, drop first row from the
' selection and clear it. If it is only one row, then keep it.
If rngChartData.Rows.Count > 1 Then
Set rngChartData = rngChartData.Offset(1, 0).Resize _
(rngChartData.Rows.Count - 1, rngChartData.Columns.Count)
rngChartData.Clear
Else

End If

Sheets("Raw Data Entry").Select
Range("A3", Range("A3").End(xlDown)).Select

'Select the range from A3 to column BF to be copied.
Set rngRawData = Selection
Set rngRawData = rngRawData.Offset(0, 0).Resize _
(rngRawData.Rows.Count, 58)

'Copy the data; paste it
rngRawData.Copy
Sheets("ChartData").Select
Range("A6").PasteSpecial (xlPasteValues)
Range("A6").PasteSpecial (xlPasteFormats)

Set rngChartData = Selection.CurrentRegion
Set rngChartData = rngChartData.Resize _
(rngChartData.Rows.Count, 58)
'rngChartdata.Select
'ActiveWorkbook.Names.Add Name:="Data", RefersTo:=Selection
ActiveWorkbook.Names.Add Name:="Data", RefersTo:=rngChartData

End Sub



Anyone with an idea?

 

[Norie]

I don't think it is anything to do with 'binding' - that is relevant when you are using other applications.

Which piece of code is actually causing the error?

Does the error always occur or is it caused by a particular set of circumstances?

 

[deo89]

The debugger points to the last line (which selects the report sheet of the workbook) of the DropDown1 subroutine. If I step throught the macro, the error occurs at the same point every time.

Essentially, the chart page of the workbook includes a drop down box for selecting the criteria you want to see visually. The chart is then updated. Again, it works on the previous version of the spreadsheet.

However, the error occurs when selecting a different criteria from the drop down list. It runs through the code and bombs at the point mentioned earlier, the last line of the DropDown1 sub-routine. Again, I'm not a VBA person at all, so I don't know if it's the previous line of code or if the VBA error is a consequence of something else. It's confusing because it all seems to point to the code (which works fine on a previous version of the sheet). The only thing that changed was the raw data and we've confirmed the formatting of the cells, etc... are all consistent.

Thanks for any input on this...