0

I am using same Splash Screen User Form for 3 years in my company but today it started giving Method 'Wait' of object '_Application' failed error. I am assuming this is related to New Microsoft Share Point (previously we were using our internal share point now using cloud)

I tried to search on SO but nothing exactly same. (i.e: Method 'VBE' of object '_Application' failed)

I tried deleting the extra paranthesis but no change...

I am thinking of adding On Error statement but if my splash screen won't pop-up why would I have this code & user form? So honestly rather than On Error statement I want to have a real solution.

Application.Wait (Now + TimeValue("00:00:01"))
SplashUserForm.Label1.Caption = "Opening..."
SplashUserForm.Repaint
Application.Wait (Now + TimeValue("00:00:01"))
Unload SplashUserForm
Application.OnKey "{F7}", "showContains"

*An Error comes straight away when it comes to Application.Wait (Now + TimeValue("00:00:01"))

UPDATE

After reviewing Stax' suggestion as per his comment once I change the Application object with CreateObject("Excel.Application") this fixes the first problem which is Wait method. But then the next line OnKey method doesn't work with CreateObject("Excel.Application").

Mertinc
  • 793
  • 2
  • 13
  • 27
  • When/where is this code invoked from? – Mathieu Guindon Jul 09 '19 at 01:37
  • @MathieuGuindon I call my user form through ThisWorkBook. Whenever anyone opens the workbook, splash screen welcomes users. – Mertinc Jul 09 '19 at 01:39
  • See if [late binding helps??](https://stackoverflow.com/questions/50957565/there-is-no-wait-method-associated-with-application-in-visualbasic-word?answertab=votes#tab-top) – Stax Jul 09 '19 at 01:44
  • @Stax but you want the *current* `Application` instance to sleep/wait, not some ad-hoc late-bound instance. Win32 `Sleep` isn't a bad idea though. – Mathieu Guindon Jul 09 '19 at 01:59
  • Another idea could be to make the workbook open on a "splash sheet" with developer/support contact info. Without gridlines, row/column headings, and no formula bar, it's just as good a blank canvas as anything else... and then you can keep it around if you want, or hide it as soon as any other sheet is activated. – Mathieu Guindon Jul 09 '19 at 02:01
  • I tried `CreateObject("Excel.Application")` rather than `Application` , it worked but rather than 1 second it takes 3-5 second for each step to load. + After this line, it gave me an error on this last line: `Application.OnKey "{F7}", "showContains"` After changing this Application with CreateObject, yes doesn't give an error but doesn't work as well. – Mertinc Jul 09 '19 at 02:05
  • `Sleep` should do what you want. – Mathieu Guindon Jul 09 '19 at 02:07
  • I couldn't manage to combine `Private Sub UserForm_Activate` and `Private Declare Sub Sleep`. When I try to write underneath of the main sub it gives me this compile error: `"Only comments may appear after End Sub, End Function or End Property"` Also another problem with `Sleep`, After `Application.Wait`, I have `Application.OnKey` - even if I work it out `Sleep`,how to solve last step? – Mertinc Jul 09 '19 at 02:19
  • Potentially try a delay function instead? https://stackoverflow.com/questions/49389093/delay-macro-to-allow-events-to-finish – Tragamor Jul 09 '19 at 10:56
  • @Tragamor I understood that the problem is not related to `Wait` method rather it's related to `Application` object. @Stax' link has given me `CreateObject("Excel.Application")` way of doing it which gradually solves the problem. (For example it fixes it on the `Wait` method. But doesn't work on the `OnKey`method. – Mertinc Jul 11 '19 at 00:59
  • Mathieu... Yes, I have only seen it in a colleagues code, and only recalled what you point out when you did. Duh!. :-). Grasping at straws now.. Tried adding "Excel." before the Application.Wait to fully qualify it?? Try having the userform modeless?? `SplashUserForm.Show (0) SplashUserForm.Label1.Caption = "Opening..." SplashUserForm.Repaint Excel.Application.Wait (Now + TimeValue("00:00:02"))` – Stax Jul 11 '19 at 05:07

1 Answers1

0

You can use Application.ScreenUpdating=False and it goes quicker.

Jenna Washburn
  • 1