Splash Screen activates too early

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,050
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
 

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,050
Office Version
  1. 2016
Platform
  1. Windows
I have CODE 2 in Sheet2, to redirect to sheet1. On workbook opens, it opens Sheet2 first and then redirect to Sheet1. Then the Splash screen is activated.

When I tried to open Sheet1 with your code (code 1), it would not trigger code 3 and the splash screen would not work. I should have explained this as it was in the workbook demo.

Code 1
VBA Code:
Option Explicit

Private Sub Workbook_Activate()
'code in workbook
    With Application
        If IsError(.ExecuteExcel4Macro("ActivateEventExecuted")) Then
            .ExecuteExcel4Macro "SET.NAME(""ActivateEventExecuted""," & 1& & ")"
            Worksheets("Sheet2").Activate
        End If
    End With
End Sub

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

Code 2
VBA Code:
Private Sub Worksheet_Activate()
'code in sheet2
 Worksheets("Sheet1").Activate ' In Sheet2 and redirects to sheet1
End Sub

Code 3
VBA Code:
Private Sub Worksheet_Activate()
'code in sheet1
UserForm1.Show 'splash screen hide
Application.Wait (Now + TimeValue("0:00:04"))

Unload UserForm1 'splash screen hide
UserForm.Show 'userform
End Sub
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Watch MrExcel Video

Forum statistics

Threads
1,127,938
Messages
5,627,722
Members
416,271
Latest member
Bolweavil

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