Splash Screen activates too early

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,049
Office Version
  1. 2016
Platform
  1. Windows
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

VBA Code:
Private Sub Workbook_Open()
Worksheets("Cover").Activate
End Sub


1607956789387.png


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,

VBA Code:
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
VBA Code:
Sub FractionComplete(pctdone As Single)

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

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,774
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

Well-known Member
Joined
Apr 22, 2007
Messages
1,049
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Apr 22, 2007
Messages
1,049
Office Version
  1. 2016
Platform
  1. Windows
This might be a possible answer, however I do not fully understand it and how to use it with my code Mr Excel
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,989
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

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

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,774
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...
Code:
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

Well-known Member
Joined
Apr 22, 2007
Messages
1,049
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

1607963528555.png
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,185
Office Version
  1. 2016
Platform
  1. Windows
How about moving the code to the activate event :
VBA Code:
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

Well-known Member
Joined
Apr 22, 2007
Messages
1,049
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Dec 5, 2002
Messages
8,185
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,735
Messages
5,626,587
Members
416,192
Latest member
steinach

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top