Forum Discussion
AppActivate give out error: "runtime error #5" if workbooks are added while ScreenUpdating = False
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
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?
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
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...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".
