Splash Screen activates too early

[Sharid]

I have an excel splash screen that opens when Sheet1("Cover ") is activated. However my problems is that the splash screen shows before excel is fully loaded and Sheet1 "Cover" visible (See Image below, Splash screen is showing behind the excel splash screen). This then causes a problem as Userform1 can not Show

Private Sub Workbook_Open()
Worksheets("Cover").Activate
End Sub

Splash Screen should only open when excel is full loaded and sheet1 is active,
Code for Splash Screen is below the splash screen has a progress bar on it and 4 x messages will pop up. All this works fine,

Private Sub Worksheet_Activate()
SplashUserForm.Show

SplashUserForm.LabelProgress.Width = 0 'Progress Bar SplashUserForm
    Application.Wait (Now + TimeValue("00:00:02"))
        SplashUserForm.Label1.Caption = [B]"Message 1"[/B]
    SplashUserForm.Repaint

'Progress Bar
    FractionComplete (0)    '(Step 1)

    FractionComplete (0.2)  '(Step 1)
    Application.Wait (Now + TimeValue("00:00:03"))
        SplashUserForm.Label1.Caption =[B] "Message 2"[/B]
    SplashUserForm.Repaint

'''''Progress Bar
    FractionComplete (0.25) '(Step 2)
    FractionComplete (0.51) '(Step 2)

SplashUserForm.Repaint
    Application.Wait (Now + TimeValue("00:00:06"))
         SplashUserForm.Label1.Caption =[B] "*** Message 3***"[/B]
    SplashUserForm.Repaint

 'Progress Bar
    FractionComplete (0.51) '(Step 3)
    FractionComplete (0.71) '(Step 3)

SplashUserForm.Repaint
    Application.Wait (Now + TimeValue("00:00:03"))
     SplashUserForm.Label1.Caption = [B]"Message 4"[/B]
    SplashUserForm.Repaint

'Progress Bar
    FractionComplete (0.76) '(Step 4)
    FractionComplete (0.96) '(Step 4)
    FractionComplete (1)    '(Step 4 Final)
   SplashUserForm.Repaint

    Application.Wait (Now + TimeValue("00:00:03"))
    Unload SplashUserForm
   UserForm1.Show '########### Since the splash screen opens before excel is fully loaded , this userform can not be found and an error happens #########
End Sub

Sub Code

Sub FractionComplete(pctdone As Single)

With SplashUserForm
   ' .LabelCaption.Caption = pctdone * 100 & "% Complete"
    .LabelProgress.Width = pctdone * (.FrameProgress.Width)
End With
DoEvents
End Sub

 

[NdNoviceHlp]

Are U sure that it doesn't have something to do with the file... your image shows that XL is repairing? Why not just get rid of the sheet activate code and simply show the userform on workbook open? Dave

 

[Sharid]

Dave
Having checked my image again, you are right it is stating repairing. This was due to the code crashing as the it would run before excel had fully loaded, as showing in the image, the image does show excel at 100%, however that was when i captured the image, you can see the green progress bar in the background is about 75%.

I did as you stated above, however I am still having the same problem.

 

[Sharid]

This might be a possible answer, however I do not fully understand it and how to use it with my code Mr Excel

 

[MARK858]

Not sure if it will make any difference in your situation but have you tried putting DoEvents in your workbook_open code.

 

[NdNoviceHlp]

Do U have addins that need to be loaded before the wb opens? The DoEvents might work or perhaps this bit of wizardry from Jon Peltier might help...

Dim t As Double
t = Timer
Do Until Timer - t > 1
  DoEvents
Loop

If none of that works, U can trial making sure that the file isn't in an error state. Close excel and open the registry editor (type regedit in the search box). Follow this path...
HKEY_CURRENT_USER\Software\Microsoft\Office" & Application.Version & "\Excel\Resiliency\startupItems\
and see if the Resiliency\startupItems\ exists and if there are some numbers there remove them.... maybe see the stuff in this link below first.
How to detect / bypass the Workbook Recovery window and process | Page 2 | MrExcel Message Board
HTH. Dave

 

[Sharid]

I have tried DoEvents, still nothing.

As the splash screen opens before excel has fully load it can not find the userform, this is the error message that I get.
I have also tried application wait time, with no luck

 

[Jaafar Tribak]

How about moving the code to the activate event :

Option Explicit

Private Sub Workbook_Activate()
    With Application
        If IsError(.ExecuteExcel4Macro("ActivateEventExecuted")) Then
            .ExecuteExcel4Macro "SET.NAME(""ActivateEventExecuted""," & 1& & ")"
            Worksheets("Cover").Activate
        End If
    End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.ExecuteExcel4Macro "SET.NAME(""ActivateEventExecuted"")"
End Sub

 

[Sharid]

Jaafar

I can not get the Splash Screen to trigger when the Cover Screen is Active I have to select another worksheet and from there redirect it to the cover sheet. See attached demo.

The Demo should work when Sheet1 is Activated, however I have to set it to Sheet2 and then back to Sheet1

Download File

 

[Jaafar Tribak]

Jaafar

I can not get the Splash Screen to trigger when the Cover Screen is Active I have to select another worksheet and from there redirect it to the cover sheet. See attached demo.

The Demo should work when Sheet1 is Activated, however I have to set it to Sheet2 and then back to Sheet1

Download File

Not sure I understand.
The splash screen works when sheet1 is activated.