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
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
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 SubThanks 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".
