I have just been upgraded to 2013 and my new machine is an IMB Surface Pro 3

Programmed spreadsheets that have worked in previous versions (2010) have stopped working with multiple errors.

I went through the active x problem and removed all buttons and replaced them with form buttons

ChDir doesn't work.

I cannot set a dimmed variable as a workbook to a name which throws out a subscript out of range error

I cannot select a named sheet again with a subscript out of range error (this affects the sheet with axtive x buttons now removed)

when I attempt an open workbooks it just spins with no response

I'm very disappointed. Is anyone experiencing a similar issue?

my code is below and has been working faultlessly for 4 years.

Sub OpenReportFileDetail()
    '***Opens a selected SAP derived Excel file and
    '***Copies data to the "Data" sheet of this workbook
    '***Calls the processing routine to range name sections
    Dim DataFile As Variant
    Dim Ans As String
    Dim a As String
    Dim m As Workbook
    Dim EndRowDat As Single

    'Set the current path and workbook names
    Application.ScreenUpdating = False
    ChDir ThisWorkbook.Path
    Mainfile = ActiveWorkbook.Name
    Filter1 = "Excel Files (*.xlsx),*.xlsx, (*.xls),*.xls"
    FilterIndex1 = 1
    Title = "Select XML File for input"
    '   Get the input XML detail report
    DataFile = Application.GetOpenFilename(Filter1, FilterIndex1, Title)
    If DataFile = False Then
        MsgBox "No DataFile was selected", vbInformation, "Nothing to Open"
        Exit Sub
    End If
        Ans = MsgBox("You selected " & _
        Chr(13) & Chr(9) & DataFile & _
        Chr(13) & Chr(13) & "Is this correct?" _
        , vbYesNo, "Check correct File is selected")
    If Ans = vbNo Then Exit Sub
    
    'If file is open then close it
    a = InStr(StrReverse(DataFile), Application.PathSeparator)
    DataFileNm = Right(DataFile, a - 1)
    
    On Error Resume Next
    Set m = Workbooks(DataFileNm)
    
    If Err.Number = 0 Then
    
    '   Close Excel file
        Application.DisplayAlerts = False
        Windows(DataFileNm).Close
        Application.DisplayAlerts = True
    End If
    
    On Error GoTo 0 'reset error trap
    
    'Open the report and get the data
    
    Workbooks.Open Filename:=DataFile
    
    'Application.ScreenUpdating = False
    Application.StatusBar = "Retrieving data from " & DataFile
        
    Windows(Mainfile).Activate
    Sheets("Data").Select
    Application.DisplayAlerts = False
    ActiveSheet.Delete
    Application.DisplayAlerts = True
    Sheets.Add
    ActiveSheet.Name = "Data"
    Range("a1").Select
    Cells.Clear

    On Error Resume Next
    Workbooks.Open Filename:=DataFile
    DataFileNm = ActiveWorkbook.Name
    On Error GoTo Endit
    
    'Copy new data from new file
    Application.StatusBar = "Copying Data"
    Cells.Select
    Selection.Copy
    Windows(Mainfile).Activate
    ActiveSheet.Paste
    Selection.ClearOutline
    Application.StatusBar = "Data pasted"
    
    'Close XML file
    Application.DisplayAlerts = False
    Windows(DataFileNm).Close
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Application.StatusBar = "Datafile closed, copy successful"

    'Format the values as number, negative, commer, two decimal
    Sheets("Data").Select
    EndRowDat = LastRow(ActiveSheet)
    Range("F1:F" & EndRowDat).Select
    Selection.NumberFormat = "#,##0.00_ ;[Red](#,##0.00) "
    Range("A1").Select
    
    'Change the colours
     TranColumn = 5
     TranLetter = "F"
    Call FixMyColour
    
    'Extra step to adjust data
    
    Columns("D:D").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
    'Subroutine to create the range names for COst Centre in the current report
    Call BuildTranRanges
      
    'Finish by recording when this step occurred
    Application.StatusBar = False
    Application.ScreenUpdating = True
    Sheets("Controll").Activate
    Range("Msg_RepDet").Select
    ActiveCell.Value = " The period transaction report import was last run at " & Now()
    Range("a1").Select
    Exit Sub
    
Endit:
        MsgBox "An error occurred." & _
        Chr(13) & "Close all files and try again." & _
        Chr(13) & Chr(13) & "Close all files and try again." _
        , vbCritical, "Process Failure"
    

End Sub

Hi,

According to your description, this issue may be caused by some factors.

First, if the Active x controls were created with Office 2010 32bit version, and you updated to Office 2013 64bit version now, they may be can't use. Office 2013 64-bit can only run the 64-bit versions of the controls.

Reference:

https://technet.microsoft.com/en-us/library/ee681792.aspx#compat4

https://support.office.com/en-sg/article/Choose-the-32-bit-or-64-bit-version-of-Office-2dee7807-8f95-4d0c-b5fe-6c6f49b8d261?ui=en-US&rs=en-SG&ad=SG

Second, it also may be caused by installing MS14-082 Microsoft Office Security Updates for December 2014. Please follow this blog to troubleshoot this issue:

http://blogs.technet.com/b/the_microsoft_excel_support_team_blog/archive/2014/12/13/forms-controls-stop-working-after-december-2014-updates-.aspx

Third, if you want to debug code, I recommend you post the question to MSDN forum:

http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

Regards,

George Zhao
TechNet Community Support