Master Class – Excel Custom Splash form

You’ve built that impressive workbook that has all the bells and whistles and all that can be automated with VBA.  So exactly how do you remind the user that this workbook is your Magnum opus (great work).  
I would suggest a subtle Excel vba splash form a.k.a. vba splash screen that pops up when the user envokes your code but a few suggestions:
♦ Flash it before the user for a second or two
♦ Keep the image simple and any words brief
♦ Consider just showing your logo as long as it identifies you.

Remember users are an impatient lot so be sure to keep it brief – 2 or 3 seconds at the most.  Ok, having made those suggestions, I am going to break my own rules in the Demonstration Excel Splash form below because I want to feature a few possibilities. The Splash form I’ve designed here has 2 dynamic properties:
1. it has a text box named lblRolling that I have posting from right to left like a stock ticker with the following messages:
sMsg(1, 0) = “Improve Quality and Consistency    “
sMsg(2, 0) = “Speed the Process   “
sMsg(3, 0) = “Improve Accuracy    “
sMsg(4, 0) = “Get Immediate Metrics   “
sMsg(5, 0) = “Improve Reliability  “
sMsg(6, 0) = “Reduce Cost   “
sMsg(7, 0) = “Reduce Key Person Dependency   “ 
2. It has bullet points that named lbl1-lbl7, lblc1-lblc7 that appear (set the visible property to true) synchronized with the ‘stock ticker’ text box.

Best of all the Splash form has no code behind it at all – it is all controlled by the code below. VBA permits the manipulation of form properties as long as the form is loaded. 

You can download my demo splash screen free, just click on the button below, download and unzip it. 

 

Excel VBA Splash Form

Learn how code in VBA a greeting splash form that will flash when the user opens the workbook. It is an opportuntiy to advertise the developer great work,

124 Downloads
Option Explicit
Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal Milliseconds As LongPtr)

Sub SplashForm()
'******************************************************
'** Date:       2/13/2019
'** Developer:  Raymond Mills
'**             Excel, Access and Word VBA Consulting
'**             www.ExcelandVBACraftsman.com
'** Subject:    Splash Greeting Form
'******************************************************

Dim sMsg(7, 2) As String, sMisc As String
Dim x As Integer, i As Integer, j As Integer, w As Integer

' build the message ...
sMsg(0, 0) = "Unleash the power of Excel VBA "
sMsg(1, 0) = "Improve Quality and Consistency    "
sMsg(2, 0) = "Speed the Process   "
sMsg(3, 0) = "Improve Accuracy    "
sMsg(4, 0) = "Get Immediate Metrics   "
sMsg(5, 0) = "Improve Reliability "
sMsg(6, 0) = "Reduce Cost   "
sMsg(7, 0) = "Reduce Key Person Dependency   "

' build the control names ...
For i = 1 To 7
sMsg(i, 1) = "lblc" & i
sMsg(i, 2) = "lbl" & i
Next i

' load and setup the form ...
Load frmBanner
frmBanner.lblRolling.Caption = ""
frmBanner.Show

'loop through our bullet points ...
For w = 0 To 7
    j = Len(sMsg(w, 0))
    i = 1: x = 1
    
    Do Until i = 500                            
    sMisc = Left(sMsg(w, 0), x)
    frmBanner.lblRolling.Caption = sMisc
    Sleep 75
    If (i + x) = j + 1 Then Exit Do
    x = x + 1
    frmBanner.Repaint
    Loop
    ' Make the bullet points and checkmarks visible ...

    If w <> 0 Then
    frmBanner.Controls(sMsg(w, 1)).Visible = True
    frmBanner.Controls(sMsg(w, 2)).Visible = True
    frmBanner.Repaint
    End If
Next w

frmBanner.lblRolling.Caption = ""
Sleep 1000
 

' clean up and close ...
frmBanner.Hide
Unload frmBanner

End Sub

VBA splash screens information and How to’s can also be found searching for:
1) VBA splash screen while macro runs.
2) VBA splash screen timer.
3) VBA splash screen excel.
4) VBA splash Form.
5) VBA splash page.
6) E
xcel vba create splash screen.
7) Create and Excel VBA splash screen.
8) VBA splash screen Timer
9) VBA splash screen progress bar

 

Raymond Mills MBA, MS

Raymond Mills, M.B.A., M.S.  has spent over 20 years of his career as Accountant, Investment Bank and Credit Card Technical Auditor/ Data Analyst.  His specialty was using Excel to get Big Databases including Teradata, Oracle, Sequel Server and Sybase to give up their secrets.

Ray has said “I love nothing better than using VBA to unleash the power of Microsoft Office.” If you have a challenge with Excel, Access or Word and would like to speak with Ray, you can get his contact details by clicking here: Contact Me 

Excel and VBA Craftsman offers experienced VBA Development: Complete GUI Driven Applications, Automated Reconciliations, VBA ODBC Database Data Mining, VBA Excel, Access, Outlook and Word Customization. VBA Custom Functions, VBA Concept Assessment, VBA integration of MS office applications into powerful single seamless solutions, Excel Help, Automate Excel, Excel Automation