AppActivate give out error: "runtime error #5" if workbooks are added while ScreenUpdating = False

[cindy_lu]

The following code will give out error.

Can someone tells me why this error occurs? :(

I found out that I can avoid error by not setting `ScreenUpdating = False` during Workbooks.Add.

But in my case, I will write a lot of new data to the added workbook. I don't want to keep updating the screen.

How can I avoid error when  setting `ScreenUpdating = False` then use `Workbooks.Add` and `AppActivate `

Sub test()
    'Application.Wait (Now + TimeValue("0:00:05"))
    Application.ScreenUpdating = False
    Workbooks.Add
    Application.ScreenUpdating = True
    Dim xl As Object
    Set xl = GetObject(, "excel.application")
    AppActivate xl.Caption
End Sub

[#2]

@cindy_lu That AppActivate code looks unnecessary to me. What exactly are you trying to do with the newly opened workbook? To do something with a new workbook, define an object variable to which you assign the newly added workbook. After that, you can refer to that object variable in your code to address the new workbook. Like so:

Sub WorkOnWorkbook()
    Dim wb As Workbook 'This is the object pointer
    Set wb = Workbooks.Add 'This adds a new workbook and at the same time adds it to the wb object variable
    MsgBox wb.Worksheets.Count 'This displays the nymber of worksheets in the newly added workbook
    wb.SaveAs "DemoOpeningWorkbookAndAssigningToObjectVariable.xlsm", xlOpenXMLWorkbook 'Save wb as file with macro's
    wb.Close 'Close the new workbook
End Sub

[#3]

In the real case, I actually create a new workbook as an object, write something into it and save it.
The execution time of my macro will take from several minutes to even 1 to 2 hours.
Thus, it is possible to have the macro running in the background then come back to the excel after the macro finished its work. I use AppActivate to make excel back to the top after the macro finished it work.

My sample code is just a short version to duplicate the error I encountered.
I just don't understand why adding workbook with `Application.ScreenUpdating = False` will make `AppActivate` raise error.
Also, I don't want to close the workbook I created. I just want to leave it open and let the user  check the output immediately.

I noticed that close the created workbook before calling `AppActivate` will make the error disappear, too...

Is it a possible bug?

[#4 · Accepted solution]

@cindy_lu OK. I tried this and it appears to work:

Sub test()
    Dim cap As String
    cap = Application.Caption
    Application.ScreenUpdating = False
    Application.Wait (Now + TimeValue("0:00:05"))
    Workbooks.Add
    Application.ScreenUpdating = True
    AppActivate cap
End Sub

[#5]

@Jan Karel Pieterse 

Thanks for the possible solution XDDD.
However, I feel that using `Wait` is really unreliable. `Wait 5 seconds` might work for PC1 but not PC2, right?

The current version of macro has something looks something like this:

Dim Wb As Workbook
Application.ScreenUpdating = True
Set Wb = Workbooks.Add
Application.ScreenUpdating = False
' #### Then Do a lot a lot of writing to `Wb`, takes several minutes to hour
Application.ScreenUpdating = True  'Make ScreenUpdating back after processing.

Dim xl As Object
Set xl = GetObject(, "excel.application")
AppActivate xl.Caption
MsgBox "Finished!", vbSystemModal  'Without AppActivate, vbSystemModal does not work for me...

[#6]

Perhaps an alternative method is needed here? If you get rid of the appactivate and just add a MsgBox statement at the end of your code:
MsgBox "Done running code"
then you can minimize the Excel application and work on other things. Once the message box is shown, the Excel "button" on the Windows taskbar will start to flash, indicating there is a "message".