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