"Forcing" Macros Enabled - Help find the loopholes

[Greg Truby]

Alrighty folks. Most of y'all are familiar with the "force the user to enable macros" trick that is explained nicely at dk's site. That's all fine and dandy until we get into the issue of folks trying to:

• save in mid-stream, i.e. not at closing time
• trying to close the workbook without being forced into saving. i.e. like myself they play all sorts of "what if's" with stuff and now they just want to close the workbook and re-open it with everything restored back to before they started playing about.

I found a nice thread started by StanSz where he discusses these conundrums. I took some of those ideas and played with them, but I still ran into some problems. So I've been fiddling around with this for much longer than I would have liked and have come up with the following code which I'm putting into a .BAS module that I can simply import into future projects. I have tested the thing about every way I can think of and at this stage it's not failing on me. But I'm wondering if I've overlooked some common scenario from my admittedly haphazard testing process. So, here is the code. If any of you dear fellow boardmembers would like to take a crack at poking holes in this (and patching them holes if you can! :wink, well I'd be much obliged for yer input.

' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' _________.xls: basxl_ForceMacrosAtOpen
' ______________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' Date:     April 2005
' Author:   Greg Truby
' Summary:  Generic module that can be imported into any workbook where macros
'           need to be enabled for the workbook to function properly and a
'           "Start Up" or "Splash" worksheet greets the user if macros are
'           disabled, giving instructions on how to enable macros &/or
'           trust content with my digital signature.
'
'           Variation on basic premise described in many place on internet
'           one example being:
'           http://www.danielklann.com/excel/force_macros_to_be_enabled.htm
' _____________________________________________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' Contents:
' ¯¯¯¯¯¯¯¯¯
'   Sub  HideSheets
'   Sub  UnHideSheets
'
'   ...copy of code to added to workbook code page...
'   Sub  wb_BeforeClose
'   Sub  wb_BeforeSave
'   Sub  wb_Open
'   Sub  wb_SheetSelectionChange
' _____________________________________________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' General Comments:
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
'   a.  Variables prefixed with a "g_" are globals.
'   b.  Variables prefixed with a "m_" are scoped to the module.
'   c.  Variables prefixed with a "c_" are constants.
'   d.  Leave only one underscore if concatenating prefixes
'       i.e. "gc_" & "mc_"
'   e.  Conditional subroutine exits should carry a         "|--¿xsub?-->"
'       on the right edge of the line.
'   f.  Conditional loop exits should carry a               "|--¿xloop?-->"
'       on the right edge of the line.
'   g.  Conditional goto's should carry a                   "|--¿goto?-->"
'       on the right edge of the line.
' _____________________________________________________________________________

'
Option Explicit

Private m_rngActive As Range, m_objActiveSheet As Object


Public Sub HideSheets()
'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯

    ' Use generic Object instead of Worksheet since
    ' the some sheets might be chart sheets.
    Dim objSheet As Object
    
    Set m_objActiveSheet = ActiveSheet
    If m_objActiveSheet.Type = XlSheetType.xlWorksheet Then
        Set m_rngActive = ActiveCell
    Else
        Set m_rngActive = Nothing
    End If
    wsStartUp.Visible = xlSheetVisible
    
    For Each objSheet In ThisWorkbook.Sheets
        If Not objSheet Is wsStartUp Then objSheet.Visible = xlSheetVeryHidden
    Next objSheet
    
    Application.Wait Now + TimeSerial(0, 0, 2)
End Sub


Public Sub UnHideSheets()
'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
    Dim objSheet As Object
    For Each objSheet In ThisWorkbook.Sheets
        objSheet.Visible = xlSheetVisible
    Next objSheet
    wsStartUp.Visible = xlSheetVeryHidden
    If m_objActiveSheet Is Nothing Then
        wsMain.Activate         ' ««« Use CodeName for Appropriate Default Sheet «««
        Range("A1").Select
    Else
        m_objActiveSheet.Activate
        If Not m_rngActive Is Nothing Then m_rngActive.Select
    End If
    ThisWorkbook.Saved = True
End Sub

'___________________________________________________________________________________
'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' Code for the WORKBOOK code page
'___________________________________________________________________________________

'Option Explicit

'Private m_booClosingTime As Boolean

'Private Sub Workbook_BeforeClose(Cancel As Boolean)
'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
'    m_booClosingTime = True
'End Sub

'Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
'    HideSheets
'    ' Don't set ONTIME if closing or if the name will be changed.  Doing
'    ' so would force open a copy of the old file.
'    If SaveAsUI Then
'        MsgBox "Select any cell or hit an arrow key to restore view.", _
'               vbInformation, "Saving as New"
'    ElseIf Not m_booClosingTime Then
'        Application.OnTime Now + TimeSerial(0, 0, 2), "UnHideSheets"
'    End If
'End Sub

'Private Sub Workbook_Open()
'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
'    UnHideSheets
'    Me.Saved = True
'End Sub

'Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
'    m_booClosingTime = False
'    If wsMain.Visible <> xlSheetVisible Then UnHideSheets  ' ««« edit to appropriate CodeName
'End Sub

{Edit}In case you're wondering about some of the 2-second delays, the workbook I was using this on was rather large. If I tested on a miniscule WB, the delays were not needed. But when I "moved to production" with a large WB, VBA would "get ahead of itself" and unhide the sheets too quickly and the saved versions had the wrong sheets visible/hidden. I would love to have some mechanism in UnHideSheets that could test whether Excel is done saving the workbook and only then proceed to unhide them, but that exceeds my knowledge at this moment.

 

[Anax]

I did it and works perfect. Thank you