Runtime Error 1004 - populating chart with form data

[poopiebear]

Hi Everyone,

I wrote some script to populate a chart from a user form. It worked in Excel 2003, but we have just recently upgraded to 2010 and I started getting this run-time error. Would appreciate someone looking at the script and providing some assistance. The error occurs after the workbook opens in line 12.

Thanks!

Private Sub CommandButton5_Click()    Dim objOLE As OLEObject
    Dim strName As String
    For Each objOLE In Me.OLEObjects
        If (TypeName(objOLE.Object) = "TextBox" And objOLE.Object.Value = "" And objOLE.Object.BackColor = &HC0FFFF) Then
         MsgBox "Please Complete All Yellow Shaded Fields", vbInformation + vbOKOnly, "Complete Data Fields"
         OK = True
         GoTo L1
         Exit For
         End If
         Next objOLE
            Workbooks.Open filename:=Range("M6")
            Workbooks("EI_Logsheet.xls").Sheets(1).Range("A" & Rows.Count).End(xlUp)(2, 1) = TextBox9.Text
            Workbooks("EI_Logsheet.xls").Sheets(1).Range("B" & Rows.Count).End(xlUp)(2, 1) = TextBox8.Text
            Workbooks("EI_Logsheet.xls").Sheets(1).Range("C" & Rows.Count).End(xlUp)(2, 1) = TextBox20.Text
            Workbooks("EI_Logsheet.xls").Sheets(1).Range("D" & Rows.Count).End(xlUp)(2, 1) = TextBox10.Text
            Workbooks("EI_Logsheet.xls").Sheets(1).Range("E" & Rows.Count).End(xlUp)(2, 1) = TextBox11.Text
            Workbooks("EI_Logsheet.xls").Sheets(1).Range("F" & Rows.Count).End(xlUp)(2, 1) = TextBox12.Text
            Workbooks("EI_Logsheet.xls").Sheets(1).Range("G" & Rows.Count).End(xlUp)(2, 1) = TextBox13.Text
            Workbooks("EI_Logsheet.xls").Sheets(1).Range("H" & Rows.Count).End(xlUp)(2, 1) = CheckBox3.Value
            Workbooks("EI_Logsheet.xls").Sheets(1).Range("I" & Rows.Count).End(xlUp)(2, 1) = CheckBox4.Value
            Workbooks("EI_Logsheet.xls").Sheets(1).Range("J" & Rows.Count).End(xlUp)(2, 1) = CheckBox5.Value
            Workbooks("EI_Logsheet.xls").Sheets(1).Range("K" & Rows.Count).End(xlUp)(2, 1) = TextBox19.Text
    If TextBox19.Text = "" Then
        Workbooks("EI_Logsheet.xls").Sheets(1).Range("J65536").End(xlUp)(2, 1) = "n/a"
    End If
    OKtoSave = True
    Workbooks("EI_Logsheet.xls").Activate
    ActiveWorkbook.Save
    ActiveWorkbook.Close
L1:
End Sub

 

[poopiebear]

Thanks for looking, but I figured it out. As the form and log sheet were developed in Excel 2003, when I updated the form as a macro-enabled workbook in Excel 2010 but did not update the log sheet in 2010, there was a conflict.

I saved my log sheet as an .xlsx and updated the code to .xlsx, then re-saved the form as an .xlsm. Voilà!