Open Workbook with Startup Worksheet

If you create a workbook that will be opened and used frequently by yourself and - especially - if you design it for sharing with other users, you may want to start it in a compelling, attractive way.

I'm presenting here an example of initializing a worksheet which you might want to appear when the workbook is being started. The workbook is macro-driven, so it must be saved as Macro-Enabled Workbook. This is how the worksheet looks like after loading with a randomly selected photo (this is a screenshot of the left part of my computer screen):

If you'd like to experiment with your own workbook, you can copy the following macro and paste it into 'This Workbook' window of your VBA Project in your workbook.

Private Sub Workbook_Open()
'The macro will run automatically after opening the workbook in which it resides
'Displays designated worksheet (front page), following the macro instructions
Dim xPhoto As Variant
Dim folder As Object, file As Variant
Dim fs, f, s
Dim fIndex As Long
Dim fList() As String, i As Long
Worksheets("Main").Activate
With Application
    .ExecuteExcel4Macro "show.toolbar(""Ribbon"",False)"
    .ActiveWindow.DisplayGridlines = False
    .ActiveWindow.DisplayHeadings = False
    .DisplayFormulaBar = False
End With
DisplayStatusBar = False
DisplayWorkbookTabs = False
Cells.Select
With Selection.Interior
    .Pattern = xlPatternLinearGradient
    .Gradient.Degree = 270
    .Gradient.ColorStops.Clear
End With
With Selection.Interior.Gradient.ColorStops.Add(1)
    .ThemeColor = xlThemeColorAccent1
    .TintAndShade = -0.250984221930601
End With
Application.ScreenUpdating = False
On Error Resume Next
fromPath = "D:\Photos\Garden\"
Set fs = CreateObject("Scripting.FileSystemObject")
Set folder = fs.GetFolder(fromPath)     '(folder spec)
ReDim fList(1 To folder.Files.Count)
f = folder.Files.Count
Randomize   ' Initialize random-number generator
fIndex = Int(Rnd * folder.Files.Count) + 1
For Each file In folder.Files
    i = i + 1
    If i = fIndex Then fList(i) = file.Name: Exit For
Next file
Set file = folder.Files(CStr(fList(fIndex)))
xPhoto = file
Range("B2").Select
If xPhoto <> "" Then
    ActiveSheet.Pictures.Insert(xPhoto).Select
    Selection.ShapeRange.LockAspectRatio = msoTrue
    Selection.ShapeRange.Height = 700#
    Selection.ShapeRange.PictureFormat.Brightness = 0.5
    Selection.ShapeRange.PictureFormat.Contrast = 0.5
    Selection.ShapeRange.PictureFormat.ColorType = msoPictureAutomatic
End If
Range("C3").Select
Application.ScreenUpdating = True
currenttime = Mid(Now(), 12, 5)
If currenttime < "12:00" Then
    g01 = "Morning" & vbCrLf & "It's " & currenttime
Else
    If currenttime >= "18:00" Then
        g01 = "Evening" & vbCrLf & "It's " & currenttime
    Else
        g01 = "Afternoon" & vbCrLf & "It's " & currenttime
    End If
End If
Beep
MsgBox "Good " & g01 & "." & Chr$(10) & Chr$(10), vbOKOnly + vbInformation, "WELCOME BACK!"
Application.Wait (Now + TimeValue("0:00:01"))
Application.ScreenUpdating = True
ActiveSheet.Pictures.Select
Selection.Delete    'Remove Photo
Cells.Select
With Selection.Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With
With Application
    .ExecuteExcel4Macro "show.toolbar(""Ribbon"",True)"
    .ActiveWindow.DisplayGridlines = True
    .ActiveWindow.DisplayHeadings = True
    .DisplayFormulaBar = True
End With
DisplayStatusBar = True
DisplayWorkbookTabs = True
Range("A1").Select
End Sub

The macro runs at the opening of the workbook. It does several things:

• activates worksheet named "Main"
• changes some of the worksheet settings
• selects randomly one of the photos located in "D:\Photos\Garden\" path on my computer
• inserts the selected photo into the selected worksheet
• determines current time of the day
• uses MsgBox to display some greeting expressions and the time
• when the user clicks OK in the message box, removes the photo and switches all initial settings back to normal
  

Before running the macro you'd need to change the worksheet name and the path to the photos (or any other pictures) location to your own selections.

These or similar features can be included e.g. in a workbook template. Such a template could, in addition, contain some formats, styles, more worksheets, print settings, text, shapes, data, graphics, formulas, charts, hyperlinks, forms, etc..