Forum Discussion
Solved
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...
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 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".
