' VBA application code for prompting users to enable macros
' Assad Ebrahim, Commercial Mathematician, 
' email: AssadEbrahim2000@gmail.com   
' URL: https://plus.google.com/u/0/+AssadEbrahim/posts
' URL: http://www.mathscitech.org/articles
' 20.Feb.2014
'
' Permission is granted to use the following code provided the attribution is preserved.
' 
' This code works for all kinds of files and allows users to save and interact with their spreadsheets as they are used to.
' All protection gymnastics are handled by the code in Module1 and the Event Handler codes in ThisWorkbook().

' Special thanks to Mark Harley for the initial concept & prototype that worked for read-only files.
' To achieve this generalisation and expansion, I have used techniques from three Excel MVP developers:
' Special thanks to :
'   Brett D J (Excel MVP 2010-2013)
'        http://stackoverflow.com/a/9425491/181638
'   Brad Yundt (Excel MVP)
'        http://www.tek-tips.com/viewthread.cfm?qid=923923
'   John Walkenbach (Excel MVP and author)
'        http://j-walk.com/ss/excel/tips/tip78.htm
'        http://j-walk.com/ss/excel/tips/tip100.htm
'
'  This code passes 7 use / test cases:
'  For each test: Open the file, enable macros, and do the test.
'  PASS = no odd behaviour and file closes quiety.
'  Tests:
'   (1) ... do nothing, close.
'   (2) ... type into cell, close, say NO at save prompt.
'   (3) ... type into cell, close, say YES at save prompt.
'   (4) ... type into cell, save, close.
'   (5) ... type into cell, close, say CANCEL at save prompt, save, close.
'   (6) ... type into cell, close, CANCEL at prompt, save, type into another cell, close, YES at prompt.
'   (7) Open the file but DON'T enable the macros...


Option Explicit

Public strSplash As String
Dim wbClosing As Boolean   ' Module scope since this is a state variable and its values must persist between the event handlers


Sub Refresh()
' Refreshes all data sources
    ActiveWorkbook.RefreshAll
End Sub

Sub special_save()  '' FRESH
    ' Save
    Application.EnableEvents = False  ' disable event trigger to prevent recursive calls to event handlers
    ThisWorkbook.Save
    Application.EnableEvents = True
End Sub


Sub restrict_access() '' FRESH

    Dim ws As Worksheet
    Dim wsSplash As Worksheet
       
    'set name of the splash screen worksheet
    Set wsSplash = Worksheets("enableMacros")
    
    ' Remove Access
    wsSplash.Visible = xlSheetVisible
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> strSplash Then ws.Visible = xlSheetVeryHidden
    Next ws

End Sub


Sub restore_access()  ''' FRESH
    Dim ws As Worksheet
    Dim wsSplash As Worksheet
    
    strSplash = "enableMacros"  ' global name
    wbClosing = False   ' Initiliase state
    Set wsSplash = Worksheets(strSplash)
    
    Application.EnableEvents = True   ' Ensure event handlers are active
           
    ' Restore Access
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> strSplash Then ws.Visible = xlSheetVisible
    Next ws
    wsSplash.Visible = xlSheetVeryHidden
        
    ThisWorkbook.Saved = True   ' Excel to ignore these last changes, otherwise it always asks before closing, even if just saved
    
End Sub



''' ==========================================================
'''    The subroutines below go into ThisWorkbook event handler, not Module1
'
' This code works for all kinds of files and allows users to save and interact with their spreadsheets as they are used to.
' All protection gymnastics are handled by the code in Module1 and the Event Handler codes in ThisWorkbook().


' Context:
'  This code is here to provide a splashscreen reminding the user to enable macros and data connections
'  No other sheets will be visible or available to them until they have done so.
'  Design:  This Workbook is an event handler, so here you will see the event handlers and wrapper functions calling
'           the code that does the work.  That code lives in Modules (together with the rest of the code's functionality)

'  Credits:
'       John Walkenbach:      http://j-walk.com/ss/excel/tips/tip100.htm
'       BradYundt @ TekTips:  http://www.tek-tips.com/viewthread.cfm?qid=923923



Sub Workbook_BeforeClose(Cancel As Boolean)
'   This sub runs when the user tries to close the file
'   Default: Cancel = False  (Close when subroutine terminates)
' Design note:  BeforeClose( ) is called BEFORE Excel checks whether the document has been saved.
' If user has not saved the document, Excel will prompt, and the user can cancel... but BeforeClose( ) will already have fired.
' So we must handle this ourselves within BeforeClose( ) before doing the cleanup...
' Credit: John Walkenbach, http://j-walk.com/ss/excel/tips/tip78.htm

    
    Application.ScreenUpdating = False  ' Freeze screens
    
    If ThisWorkbook.Saved = False Then  ' Dirty file
        a = MsgBox("Do you want to save the workbook?", vbYesNoCancel)
        If a = vbYes Then
            Call restrict_access
            Call special_save  ' and then close
        ElseIf a = vbNo Then ThisWorkbook.Saved = True  ' Excel ignore any changes made, then Close
        Else: Cancel = True ' Don't close
        End If
    End If
    
    Application.ScreenUpdating = True ' Restore screens as before
    
    ' Else Workbook Closes
        
End Sub

Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'  BeforeSave allows us to sandwich the user save action with a call to remove the access before the save
'     so that the saved down sheet is protected for the next time it opens, and then restore the access after the save
'     so that the user can continue working
'  Cancel is False when the event occurs.  If it is set to True before the end of the procedure, then there is no Save afterwards.

    Dim wsCurrent As Worksheet
    Set wsCurrent = ActiveSheet
   
    Cancel = True  ' Don't run the windows save because we're running it here
    
    Application.ScreenUpdating = False ' Freeze Screens
    Call restrict_access
    Call special_save
    Call restore_access
    Application.ScreenUpdating = True ' Restore screens as before
    
    wsCurrent.Activate

End Sub


Sub workbook_open()
'   workbook_open( )  only runs when the workbook (spreadsheet) first has macros enabled (allowing the code to run)
'   Therefore, pre-saving down the worksheet so that only a reminder to enable macros is visible on startup
'   and putting in code to reverse this after the user has enabled macros is a brilliant idea.
'   Thanks to Mark Harley for the approach and initial prototype that worked fine for read_only files.
'   What follows is an extended version that takes into account users interacting and saving their work.
'   New mechanisms:
'       xlSheetVeryHidden   is an attribute that the user cannot bypass from the main UI if they decline to enable macros...

    Application.ScreenUpdating = False  ' Freeze screens
    Call restore_access  ' Very Hide splash screen, unhide other sheets
    Application.ScreenUpdating = True ' Restore screens as before
End Sub