Excel 2013 wrong window activated from VBA

Dear alll,

I have a curious problem - I have a complex VBA project, part of which allows users to export 5 worksheets to a new workbook, after which the new workbook is activated. This works fine in Excel 2010 but when I run the code in 2013, 2 strange things happen:

1. the nb.Activate line in the code seems to activate the originating workbook and vice versa, ThisWorkbook.Activate shifts focus to the new workbook;

2. when clicking on the X to close the window of the new workbook, the originating workbook closes instead.

It's very frustrating and seems to be a problem only in Excel 2013 - it'd as if the Application processes are getting confused about the identity of the two workbooks but even when I use the code Workbooks("NewBook.xlsx").Activate instead, it still activates the wrong workbook. Has anyone else encountered this?

It is worth noting that this is happening while the originating workbook has two Userforms open in it - this could be a factor.

Any thoughts?

Ok, I have removed some of the code as it is proprietary, but here is the essence of it:

Option Explicit
Global ExportType$, S$

Sub ExportToXL()
On Error GoTo errorhandler

Dim w As Worksheet, nb As Workbook, n As Long, ThisCell$, nm As Name, z As Byte, i As Long, FirstRow As Long, LastRow As Long, NewPackNames$ ', wName$
Dim Area As String, timestamp As String, wbName As String, ActRge As String, Append As String, DelData As String, SelCell As String, Locked As Boolean

Application.ScreenUpdating = False

S = ThisWorkbook.FullName
S = Replace(S, ThisWorkbook.Name, ("Data Extracts" & Application.PathSeparator))

If Len(Dir(S, vbDirectory)) = 0 Then
    MkDir S
End If

Area = GetLocName() '### 19-Oct-2013 ALG

If InStr(Area, "(") Then
    Area = Trim(Left(Area, InStr(Area, "(") - 1))
Else
    If Len(Area) > 28 Then
    
        Area = Left(Area, 28)
        Area = Trim(Left(Area, InStrRev(Area, " "))) & "..."
    End If
End If


wbName = Area
S = S & wbName

Beginning:

Area = GetLocName()

Application.CutCopyMode = False

If Not FileExists(S & ".xlsx") Then 'checks if file exists
    Application.ScreenUpdating = False
    Set w = expExcel3
    w.Visible = xlSheetVisible
    w.Copy
    Set nb = ActiveWorkbook
'    wName = nb.Name
    w.Visible = xlSheetVeryHidden
    
'exports sheets to new workbook nb
    For n = 2 To 1 Step -1
        With ThisWorkbook.Sheets("ex_" & n)
            .Visible = xlSheetVisible
            .Copy before:=nb.Sheets(1)
            .Visible = xlSheetVeryHidden
        End With
    Next n
    
    nb.Sheets("ex_1").Activate
'code here

    For Each nm In nb.Names
        If InStr(nm.RefersTo, "[") Then nm.Delete
    Next nm
    
    Application.DisplayAlerts = False
    nb.SaveAs S & ".xlsx"
    Application.DisplayAlerts = True
    
Else

    ThisWorkbook.Activate
    Application.ScreenUpdating = True
    If guiControls.Range("B24").Value = True Then
        Dim YesNo$: YesNo = MsgBox("Would you like to view the dashboard with the existing data?", vbYesNo + vbApplicationModal, "Existing data export")
        If YesNo = vbYes Then
            Application.Windows(nb.Name).Activate
            Exit Sub
        Else
            nb.Save
            Application.DisplayAlerts = False
            nb.Close
            Application.DisplayAlerts = True
            ThisWorkbook.Activate
        End If
        
        Exit Sub
    End If
     
    Append = MsgBox("A data extract already exists" & vbCr & vbCr & "Click Yes to append the new data to the existing tables" & _
                    vbCr & "Click No to create a new data export to replace the old data", _
                    vbYesNoCancel + vbApplicationModal + vbQuestion, "Existing data output")

    If Append = vbCancel Then
        ThisWorkbook.Activate
        Exit Sub
        
    ElseIf Append = vbYes Then
        Dim LastPack As String, NewPack As String, SearchPack As String, shName As String
        Dim m As Long, q As Long, RowDiff As Long, Insertrows As String, StrSourceRge As String, SearchCol As String, NewRow As String, CopyRange As String
        Dim FirstRowSource As String, SourceRange As Range, TargetRange As Range
        
        Application.ScreenUpdating = False
        
        If Not FileOpen(Area & ".xlsx") Then Set nb = Application.Workbooks.Open(S & ".xlsx")
        '-- open existing file
        ProgressBar 0.1
        
        For n = 1 To 2
'code here
                With ThisWorkbook.Sheets("Package " & n)
                    .Visible = xlSheetVisible
                    .Copy after:=nb.Sheets(nb.Sheets.Count)
                    .Visible = xlSheetVeryHidden
                End With
                
        Next n

    Else
        Application.DisplayAlerts = False
        If FileOpen(Area & ".xlsx") Then Workbooks(Area & ".xlsx").Close
        Kill S & ".xlsx"
        Application.DisplayAlerts = True

        GoTo Beginning
    End If
End If

If InStr(nb.Name, Area) = 0 Then nb.SaveAs S & ".xlsx"

If nb.Sheets("ex_1").Visible = True Then nb.Sheets("ex_1").Visible = xlSheetVeryHidden
If nb.Sheets("ex_2").Visible = True Then nb.Sheets("ex_2").Visible = xlSheetVeryHidden
nb.Sheets("Metric Dashboard").Activate
Workbooks(nb.Name).Save

Application.ScreenUpdating = True

DoEvents
Application.Windows(nb.Name).Activate

Exit Sub
errorhandler:

ErrorTrap "#050001", Err, Right(S, Len(S) - InStrRev(S, Application.PathSeparator)) & ".xlsx"
End Sub

As may be apparent, there is a lot of jumping back and forth between the workbooks (hence not relying on the new book being active as a result of being created) but I have clearly set the new book back to being active at all necessary exit points...

So yeah, puzzled. As I say, it works in 2010...

I was trying a variety of different approaches, so in the code pasted here it actually uses "Application.Windows(nb.Name).Activate" instead - but the outcome is the same.

As for why jumping between workbooks, it is because changes are being made to both in the course of the code that has been omitted - it takes about 5-10secs to process everything, during which time the original workbook is displayed. Only at the end, once the export has completed, should the new workbook be visible and activated.

As I say, the code works perfectly in 2010.

I have and the frustrating thing is that nb is being referenced correctly so far as I can tell (using a debug.print to check the name)...

I think I have solved the problem - it seems to be related to the fact that the Excel application windowstate is Maximized.

When both the originating workbook and the exported workbook are maximised and I click on the X to close the exported file, it actually closes the original workbook, however, if I set the windowstate to normal, it closes the correct workbook.

What I have done, then, is to set the Application.Windowstate = xlNormal, activate the new workbook, then reset Application.Windowstate = xlMaximized. This forces the applciation to activate the correct window.

I'm fairly sure that this is an issue with Excel 2013 - do you have any thoughts?