Excel 2013/2016 SDI Issue with Workbooks.add

[ChrisDaniels]

Hi all

This is really foxing me and I'm hoping someone can help before I pull all my hair out!

In 2013/2016 creating a new workbook will leave the focus on ThisWorkbook even though it is the ActiveWorkbook, and I cannot seem to get around it.

The reason this is causing an issue is that in several tools that I maintain, they will spring off new workbooks if the user clicks a button to do so.
If the user wants to then save this new book or close it, it will save the original book or close the original book as the background focus is still on the original book!

I know its something to do with the new SDI that Microsoft built in from 2013, but I cannot figure out how to work around it for new workbooks - the only help MS seem to offer relates to userforms.

Example code to replicate:

Sub TestCreateNewBook()
Dim NewBook As Workbook
Set NewBook = Workbooks.Add
NewBook.ActiveSheet.Activate
Set NewBook = Nothing
End sub

Then immediately go to Save As and note the filename in the prompt, or Close using the top right cross and note the filename in the prompt there.

 

[Yongle]

Unable to recreate you issue
Perhaps something along these lines to force user to save the file immediately

ChDir allows you to change the working folder to make dialog box open in folder you want

Sub TestCreateNewBook()
       Dim newbook As Workbook, UserInput As Variant
       [COLOR=#006400]ChDir [/COLOR]"C:\Folder\SubFolder"
       Application.ScreenUpdating = False
       Set newbook = Workbooks.Add
       UserInput = Application.GetSaveAsFilename(FileFilter:="Excel Files (*.xlsx), *.xlsx")
       If UserInput <> False Then newbook.SaveAs Filename:=UserInput, FileFormat:=51
End Sub

 

[ChrisDaniels]

Thanks @Yongle, I already do this, but its not ideal.

I assumed it was the same in 2016 but from google searches, it maybe resolved there.

 

[ChrisDaniels]

I think I have a workaround for anyone in the same boat.

Call this at the end of your code:

Sub WindowHelper()
     Dim ActiveWindow: Set ActiveWindow = Application.ActiveWindow
     Application.ScreenUpdating = False
     ActiveWindow.Visible = False
     ActiveWindow.Visible = True
     ActiveWindow.Activate
     Application.ScreenUpdating = True
End Sub

 

[Yongle]

In that case, how about...

Sub TestCreateNewBook()
    Dim newbook As Workbook
    Application.ScreenUpdating = False
    Set newbook = Workbooks.Add
    Windows(newbook.Name).Activate
    etc...
End Sub

 

[rlv01]

My copy of Excel 2016 does not behave as you describe. If I run your test code then select Save-As, the WB name is the newly added WB, just as it is under XL2010.

 

[ChrisDaniels]

My copy of Excel 2016 does not behave as you describe. If I run your test code then select Save-As, the WB name is the newly added WB, just as it is under XL2010.

Thanks, yes I think this issue is just with 2013. I assumed it was also 16 because of the SDI change