home link

 

 

Synopsis

This article is a comprehensive discussion of the most common strategy for forcing a user to enable macros in an Excel workbook. Here is an outline of the article's contents

  • a pictoral explanation of the logic behind this solution
    • a normal workbook
    • adding the splash screen
    • hiding all but the splash
    • unhiding all but the splash & hiding the splash
  • why we use xlSheetVeryHidden instead of xlSheetHidden
  • four (4) recommended articles that discuss how to implement this methodology
    • Mike Alexander's
    • Domenic Tamburino's
    • Ken Puls'
    • Chip Pearsons
  • the various pitfalls when implementing this methodology
  • a comparison / summary of the authors' implementations
  • the Excel Plaza implementation of this concept
  • links to parts II & III of this article

Forcing Macros to Be Enabled - Part I

When I worked for Sony I created this amazing workbook that did all sorts of wonderful things as you clicked various controls and buttons.   I showed my super duper workbook to Russ, my boss, who was suitably impressed. Indeed, Russ was so impressed that the next day he came to my desk and said “I tried to show Fujishima-san (our vice-president, and Russ’ boss) your Excel report last night after you’d left.  But I couldn’t get it to work.”

Russ had done what many of our workbook users do all too often – he’d dismissed the “enable macros” warning without so much as reading it, thereby causing all my wonderful VBA wizardry to become useless.

So let's take a look at how we can make sure your users enable the macros you've so painstakingly added to your workbookwe and avoid you going through a replay of what happened with my boss and my VP.

List of Articles

Where Things Go Wrong

Comparison Table

The Excel Plaza Version

So What Exactly Are We Trying to Do Here?

Most “force the user to enable macros” take the following approach.  We’ll start with a rather common looking set of worksheet tabs — one that looks like so:

screenshot of normal worksheet tabs

Pictorially, we might express that like so:

graphic of normal worksheets

What we want to do is to add a “splash sheet” that will tell the user that they need to enable macros. So now we have:

graphic of worksheet with splash screen

For now our splash sheet is just something very simple. We’ll gussy it up later.   So our actual workbook looks something like this:

screenshot with splash added

What we want to do is to figure out a way to show the user only the splash screen.  What we do is utilize the workbook’s Workbook_BeforeSave() event handler.  Every time we save the workbook – before we do the save, we are going to run a macro that will hide all the worksheets and show only our splash screen. So we have this:

graphic of hiding sheets

After we get done doing the save, we restore the visibility of all the other sheets and hide the splash sheet.

graphic of unhiding sheets and hiding splash

In the course of doing all of this hiding and unhiding of sheets we’ll turn off Excel’s ScreenUpdating property so the user won’t notice any changes going on.  But since we hid all of the other worksheets, when the workbook opens, all he will see is the splash screen

screenshot of hiding sheets

Hopefully you noticed that when we hide the sheets before the save and then unhide after the save, we used the constant value xlSheetVeryHidden and not the standard level of hiding a worksheet which would be to set the sheets’ visibilities to xlSheetHidden.  Why?  Because if macros are disabled we don’t want to give the user the option of unhiding the other sheets manually using the user interface.  A clever user could use either the home tab or the right-click menu from a worksheet tab to call up the Unhide dialog box manually.

ribbon unhide sheets
The Unhide Sheet... option in the Ribbon

     

right click menu for worksheet tabs
The Worksheet Tab Right-Click Menu

  

the unhide (sheets) dialog box
The Unhide (Sheets) Dialog Box

If we use xlSheetVeryHidden to hide the sheets and the user tries this, Excel will pretend like those sheets do not even exist and the user will see this:

ribbon unhide sheets
NOTE that the Unhide Sheet… option is disabled

     

right click menu for worksheet tabs
Again, the Unhide… option is disabled

Notice that the Unhide… option is greyed out because there are no “hidden” sheets, only “very hidden” sheets.  Yes, a very clever user well-versed in VBA could write some code in the VBE that would unhide them in a trice.  But any user clever enough to know how to do that would understand that he needs to enable macros for the workbook to function properly and if he hasn’t enabled macros, then there›s probably a good reason why.  And let›s be honest, you›re not doing this for advanced users. You›re doing this for those users like my old boss; users who will fail to enable macros and then wonder why nothing works.

And Now Some Light Reading

Alrighty then! Now that we’ve walked through what we’re going to do, it’s time to turn the discussion over to several excellent authors who have written articles on this subject. While there are many articles floating around the Internet on this topic, below are four that are written by people I know personally or I am very familiar with their work.  I recommend reading the articles in the order they are listed: Mike's, Dom's, Ken's & then Chip's.

Also, although I rather expect that each of these author’s web pages will be out there on the internet for a long, long time, I am providing links to textfiles with the code that they have in their article or downloadable workbook just in case, at some unforeseeable future, their sites are no more… If they do not offer a downloadable version of an Excel workbook with their code, I’ve created workbooks with their code and added links for you to download them.

Articles

Code Blocks

Mike — View MA’s VBA Workbook Module Code as Text File


Domenic — View DT’s VBA Workbook Module Code as Text File


Ken — View KP’s VBA Workbook Module Code as Text File


Chip 1 — View CP’s VBA Workbook Module Code as Text File


Chip 2 — View CP’s VBA Standard Module Code as Text File




Downloadable Workbooks

Mike — Download MA’s “Force Macros Enabled” Workbook


Domenic — Download DT’s “Force Macros Enabled” Workbook


Ken — Download KP’s “Force Macros Enabled” Workbook


Chip — Download CP’s “Force Macros Enabled” Workbook



Where Can Things Go Wrong?

Now that we’ve run through four author’s articles on the subject, let’s take a look at where things can go wrong with this whole “Splash-Sheet-to-Enforce-Macros” gambit.

Choosing the Correct Event(s)

For the process that hides the splash sheet and unhides the rest of the worksheets, it looks like we might have a couple of possibilities - the Workbook Open event or possibly the Workbook Activate event. However we really only need to hook into the Open event. Hooking the Activate event wouldn't gain us anything unless we were doing something with the Workbook Deactivate event, which we aren't.

And what about the other half of the equation?  The part where we hide all but the splash sheet?  It appears that we have two event procedures we could use: Workbook Before Close event or the Workbook Before Save event. In this case it’s not either-or, it’s both.

So these are the events we need to hook:

  • Private Sub Workbook_Open()
  • Private Sub Workbook_BeforeClose(Cancel As Boolean)
  • Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

So why do we need these three particular events?  Well, hopefully the Workbook Open is fairly self-evident.  Every time we save the workbook, we hide all the worksheets except our splash sheet.  Then, after the save, we go ahead and unhide them again.  So when we open the workbook, only the splashsheet is visible and macros are needed to make the other sheets visible.  That’s the whole point of what we’re doing here.  We don’t use the Activate/Deactivate pair of events because that would be overkill.  We haven’t any need to go toggling visibilities every time the workbook is deactivated.  Indeed, we this is actually something we wouldn’t want to do.   Because if the user were to use the View tab to tile workbooks and then activates a different workbook, the last thing he’d expect is for the sheets in our workbook to suddenly disappear and for him to only be able to view the splash sheet.

So now let’s take a look at why we need both Workbook Before Close event and the Workbook Before Save event.

save changes dialog box

Probably the easiest way to explain why we need both is to look at what happens if we don’t use both.  If we use only the Before Save event, and we don’t put any code at all in the Before Close event, then when we go to close the workbook and there have been changes, Excel is going to ask us if we want to save changes.  If we say “Yes”, then the Before Save event code runs.  However even if the Before Save event code sets the ThisWorkbook.Saved property to TRUE, Excel will cycle back as if it were not set to TRUE and show the “Do You Want to Save Changes” dialog again and again and again.  A workbook using the code exactly as Ken Puls has it in his article, with no modifications, gets trapped in this loop. Eventually the user will just say “phooey” and click “Don't Save”.  Everything will be okay, the workbook’s changes actually got saved.  But the loop makes the user think he didn’t save changes properly. So we need to put some sort of code in the Before Close event handler to make sure we don’t subject the user to this loop.

Now, having said that we must put some code inside the Before Close event handler, we need to be aware of one other aspect of this. We need to allow the user to close the workbook without saving changes.  In the examples above, Mike Alexander’s sample workbook does not offer the user the ability to close the workbook without saving changes. This can be a pretty serious problem.  Suppose the user has just accidentally deleted a whole bunch of data, or an entire worksheet and he wants to close the workbook with saving his blunder.  If we force a save upon close we take away this ability to close a workbook without saving unwanted changes.  We (and our good buddy VBA) are not going to win many popularity contests if we do much of that.

And why do we need to trap in the Before Save event?  Because if we don’t, what can happen is we just hit Ctrl+S and we save the workbook with our splash screen hidden and our normal worksheets showing.  Then when we close our workbook we click “No” on saving any further changes.  Then, if we later open the workbook with macros disabled, our splash screen isn’t showing to “force” our user to enable macros.  This can be harder to test if Excel is using the “trusted documents” setting.  Because you’d have to save the workbook and then copy it to a new folder where macros weren’t trusted and open it with macros disabled to see it happen.  A workbook using Chip’s code with no modifations will suffer this weakness.

Restricting the File Type

This didn’t used to be a problem back when everything was XLS file formats. But with the advent of XLSX & XLSM file formats beginning with Excel 2007, this is now an issue.  We create the wonderful workbook that has all of these super-fancy macros and then all our user has to do is click Save As and pick XLSX for the file format and bahda-bing bahda-boom all all our darling little macros go right out the window! 

If you review the code in Domenic’s article and in Ken’s, you’ll see that they both address this issue. Ken handles this by restricting the user to only the XLSM format. Domenic’s code takes a look to see if the macro is running in a version of Excel below 2007 (Excel 2007 = version 12). If so then it only allows the user to save in XLS. If version 14 or higher, then allow XLS or XLSM.

Can the Splash Sheet Be Renamed?

While it’s certainly not a common failure point, it is a possible weakness in most programmers’ implementation of the “Splash Sheet Protocol”. And that weakness is to rely on the splash sheet’s NAME property. A much more robust means referencing the splash sheet is to key off its CodeName property. A playful user might decide to try renaming the splash sheet to see what happens. (Remember that simply hiding the sheet tabs does not prevent a user from renaming sheets via the HOME tab in the ribbon.) All four of our authors above neglected to address this Achilles’ heel, choosing instead to reference their splash sheets via the .Name property.

Assumes That Only the Splash Sheet is Hidden

A very common mistake that programmers make when implementing the “Splash Sheet Protocol” is – when doing the “unhide all but the splash sheet” bit – to assume that the rest of the sheets in the workbook should be visible. It is entirely possible that other worksheets were hidden and even possibly VeryHidden and now we’ve gone and made them all visible. Three of our four authors above made this mistake. Only Chip avoids tripping up on this stumbling block.

Looping the Wrong Collection

Another common mistake is to loop the Worksheets collection instead of Sheets collection. If we loop the Worksheets collection we run the risk of failing to hide any Chart sheets. (There’s also a miniscule risk we might fail to hide an Excel 4.0 Macro Sheet or an Excel 5.0 Dialog Sheet, but chances are if your workbook has those, you put them there and so you would know that you needed to handle them. On the other hand, there’s a reasonably good chance a user might just decide to add a chart to the workbook.) If we loop the Sheets collection we’ll pick up any chart sheets in the workbook.

Handling A Protected Structure

We should write our code such that if we decided to protect the workbook structure, our code can unlock the structure, make the changes to the sheets’ visibilities and then relock the workbook. We should also be able to elegantly handle a situation where the user decides to add workbook-structure protection with a password.

Keep Our Macros Private

Most implementations of the “Splash Sheet Protocol” have all of the code related to this process in the Workbook Code Module.   By default procedures in that module are Private and therefore are not shown in the Macro Dialog Box (Alt+F8). However if we should opt for a structure like what Chip Pearson used, where the code is in a Standard Module then we need to be sure we put an Option Private Module statement at the top of the module containing our HideAllSheets and UnhideAllSheets procedures. This statement will keep these key procedures from showing up in the Macro Dialog Box.

A Word of Caution About ActiveWorkbook

This is not something that is unique to the “Force Users to Enable Macros” protocol. It’s something that could happen anywhere and does from time to time. And it just so happens that it happened here. Sometimes programmers will use the ActiveWorkbook keyword as if it were interchangable with the Me keyword in a workbook's code module or the code name for a workbook (the default is ThisWorkbook which should rarely be altered). But ActiveWorkbook is not the same as Me (in a WB module) or ThisWorkbook

For Example: If I were to write the following code, drop it into some new workbook and then run it…

Sub CloseTheRest()

Dim wb As Excel.Workbook

ThisWorkbook.Activate

For Each wb In Application.Workbooks

If Not wb Is ThisWorkbook Then

Debug.Print "preclose: wb = "; wb.Name, " | active = "; ActiveWorkbook.Name
wb.Close False
Debug.Print "Post Close Active: "; ActiveWorkbook.Name & vbCr & String(30, "-")

End If

Next wb

End Sub

…the code in Mike Alexander's workbook module will save the wrong workbook before it closes because the assumption ActiveWorkbook IS Me IS ThisWorkbook has crept into the code. Please don’t think I’m singling Mike out here. His code was not the first time this ever happened, nor will it be the last. It just so happened that his code provides us with a good working example of something that can happen. I have made – and, I fear continue to make with astonishly frequency – similar mistakes myself.  Really just giving you a “heads up” here.

Nice To Haves

These aren’t really places where using the ol’ forcing macros to be enabled via splashsheet trick fails so much as places where it can be a bit annoying. One is screen flicker. You want to turn off the application.screenupdating property before you do all the hiding and unhiding of sheets.

The other annoyance can be the active sheet. Users are accustomed to reopening a workbook with the same sheet active as when they saved the workbook. If you fail to have your Unhide Sheets macro restore the active sheet to whatever it was before they saved the workbook, it will be annoying – especially when they save, but don’t close the workbook.  If a user clicks Save, the last thing she expects is for the workbook to suddenly jump to a completely different sheet.

Summary of Issues When Forcing Macros to Be Enabled
Issue DataPig XL-Logic ExcelGuru CPearson
Traps the Workbook Before Save Event red flag green flag green flag red flag
Traps the Workbook Before Close Event green flag green flag red flag green flag
Does Not Force User to Save Unwanted Changes red flag green flag green flag green flag
Blocks User from Saving as XLSX File Format red flag green flag green flag red flag
Will Not Fail if User Renames the Splash Sheet red flag red flag red flag red flag
Allows Sheets Other Than Splash to Be Hidden or Very Hidden red flag red flag red flag green flag
Can Handle Workbooks with Structure/Windows Protection red flag red flag red flag green flag
Hides All Sheets, Not Just WORKsheets red flag green flag red flag green flag
Are the HideAllSheets/ShowAllSheets Macros Visible From Macro Dialog?
(blue flags = standard modules not used, i.e. not applicable)
blue flag blue flag blue flag red flag
Suppresses Screen Updating While Hiding/Unhiding red flag green flag green flag red flag
Restores Workbook’s Active Sheet After Saving red flag green flag green flag red flag

The Excel Plaza Version of This Concept

So by now you’re probably thinking “All right there, Mr. Smartypants, how would you do it?”  Well, if you’ll kindly have a seat, sip your drink and help yourself to some more chips and salsa, we’ll go through exactly that. What we’re gonna do is borrow a few ideas from each of our various authors and mix in a bit of our own code and hopefully, by the time we’ done, we will have concocted a solution that will check off all the tickboxes in our summary table above.

When I started trying to design a solution that was more comprehensive than any shown in the four articles I referenced I quickly realized this was what I call a "Shrek of a problem". If you're familiar with the movie, you know that ogres are like onions – they have many layers – and this problem was a many-layered ogre all right. I very much want to go on record say that were it not for the work that Ken, Domenic & Chip had done, this would have taken much longer. As you can see from the comparison table there is at least one green flag in most of the rows. All I had to do was weave their solutions into my code.

I really liked Chip’s idea of creating some sort of a state save so we could restore, i.e. we weren't just assuming all the other sheets besides our splash sheet are to be visible. However as I thought about it, I sort of wanted to play with the concept of a “default” visibility. Allow the user to hide or unhide some sheets, but upon save/re-open to reset their visibilities. The more I thought about it, the more I moved away from the “stash values in a parsable hidden name” structure and towards a table-driven structure. [If you’re familiar with Professional Excel Development (first edition) it’s basically borrowing a play from what Bullen and Bovey did in their table-driven commandbar builder.] So that’s what you see here: a table-driven approach.

In most of my projects I use Centralized Error Handling (CEH) and you'll code that is wired for that in the error handlers in each routine. However adding CEH into this project was farther than I was willing to go. So I used another error handling tool that I have built. One for handling "light-weight" errors. It keeps from cluttering up code with all sorts of "oopsies" messages inside the core routines and moves that code off to a support routine. This means we ended up with three (3) code modules in the workbook:

  • The Workbook Code Module
  • The Main “Force Macros Enabled” module
  • The Error Messenger module
The Workbook Code Module
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' «| Workbook Code Module |»
' _____________________________________________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' module: ThisWorkbook
'
' date: Jan 2014
'
' author: Greg Truby | www.gabraria.com | www.excelplaza.com
'
' summary: Workbook code module for example workbook where demonstrating
' how to "force" users to enable macros.
'
' This code should have come from the following URL:
'
' http://www.excelplaza.com/ep_truby/Article_002_Forcing_Macros_Enabled_Part1
' /truby_002_force_macros_enabled_p1.php
'
' Please leave the above URL in this module's comments in
' case I ever need to issue revisions to the code base to
' fix any bugs or improve robustness.
'
' Concepts and/or code have been borrowed from one or more
' of the following URLs:
'
' http://www.cpearson.com/excel/EnableMacros.aspx
' http://www.xl-central.com/force-users-to-enable-macros-in-a-workbook.html
' http://www.excelguru.ca/content.php?162-Force-User-To-Enable-Macros
' http://datapigtechnologies.com/blog/index.php/forcing-your-clients-to-enable-macros/

' _____________________________________________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' copyright / work-for-hire status: [public/open]
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯ ¨¨¨¨¨¨¨¨¨¨¨¨¨
' The code in this module is either taken entirely from public sources on the
' internet or is a combination of public sources with some enhancements made
' by GabrAria LLC or code written by GabrAria with the intention of making the
' code publicly available on the internet at a future date.
' Therefore none of the code in this moduel is copyrighted by GabrAria LLC
' and to GabrAria's knowledge it is not copyrighted by any other author(s).
' _____________________________________________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' • see module basZZ_ProjectNotes for comments specific to this project
' • see module basZZ_GeneralCodingNotes for comments regarding general programming
' and coding conventions used.
' _____________________________________________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' contents:
' ¯¯¯¯¯¯¯¯¯
' Sub Workbook_BeforeClose
' Sub Workbook_BeforeSave
' Sub Workbook_Open
' _____________________________________________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Option Explicit


'··············································································
' • • • M O D U L E - L E V E L D E C L A R A T I O N S • • •
'··············································································

'// __constants_________
'// ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
'// __strings___________

'Private Const mc_strThisModule As String = "Workbook Code Module" '// for error logging

'// ____________________
'// ¯¯constants¯¯¯¯¯¯¯¯¯

'··············································································
' · · · e n d o f m o d u l e - l e v e l d e c l a r a t i o n s · · ·
'••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••


' _____________________________________________________________________________
' BEFORE CLOSE
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' Descrip: If there are changes to be saved, make sure our saving process
' handles the save.
'
' Args: Cancel· · · · · passed by ref. set to TRUE if want to cancel the
' workbook closing action and leave the workbook open.
'
' Returns: n/a
'
' Date Developer Comments
' ¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨
' 30 Dec 2013 G. Truby • initial version
' _____________________________________________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Private Sub Workbook_BeforeClose(Cancel As Boolean)
' _____________________________________________________________________________

Dim booSuccess As Boolean, _
intResponse As Integer

Do While Not Me.Saved

Let intResponse = MsgBox("Would you like to save the changes you made to " _
& vbCr & "'" & Me.Name & "' ?", _
vbQuestion + vbYesNoCancel, _
"Unsaved Changes")
Select Case intResponse
Case vbYes

'// if user decides not save here then will be asked again
'// if he wants to save changes

Let booSuccess = fnRecordStateAndHide

If Not booSuccess Then
ErrMsgBox errFME_ProblemAtClose, vbCritical
Cancel = True
Exit Do
End If

Let booSuccess = fnSaveThisWorkbook

If Not booSuccess Then
'// from the BeforeClose there is no opportunity for SaveAsUI
'// to be true, so there was some problem during the save.
ErrMsgBox errFME_ProblemAtClose, vbCritical
Cancel = True
Exit Do
End If

Let Me.Saved = fnHideSplashAndShowNormalSheets

Case vbNo
'// they don't want to save the changes, we can
'// just go ahead and close the workbook.
Me.Saved = True

Case vbCancel
'// they want to cancel the close. we don't set
'// me.saved to true to exit the loop because
'// if they then turn around and click close we
'// still want to ask them if they want to save changes.
Cancel = True
Exit Do
End Select
Loop

End Sub '// Before Close

' _____________________________________________________________________________
' WORKBOOK BEFORE SAVE
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' Descrip: Before we save we need to hide all the sheets except the splash,
' make sure the user doesn't save as an XLSX format and then
' restore the sheet visibilities.
'
' Args: SaveAsUI· · · · TRUE if the user is doing a SAVEAS; FALSE if the
' user is simply doing a normal SAVE.
' CANCEL· · · · · Passed by reference, if change to TRUE the default
' save action is canceled and the workbook's SAVE
' method must be invoked elsewhere if the save is
' to occur.
'
' Returns: n/a
'
' Date Developer Comments
' ¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨
' 02 Jan 2014 G. Truby • initial version
' _____________________________________________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
' _____________________________________________________________________________

Dim booDidWeSave As Boolean, _
booScreenUpdating As Boolean

'// we'll cancel the normal save action...
Let Cancel = True

With Application
Let booScreenUpdating = .ScreenUpdating
.ScreenUpdating = False
End With

If Not fnRecordStateAndHide Then
ErrMsgBox errFME_ProblemBeforeSave, vbCritical
GoTo CleanUp ' |--¿goto?-->
End If

'// ... and then process the save action through our custom routine
Let booDidWeSave = fnSaveThisWorkbook(SaveAsUI)

If Not fnHideSplashAndShowNormalSheets Then
ErrMsgBox errFME_ProblemAfterSave, vbExclamation
End If

Me.Saved = booDidWeSave

CleanUp:
'¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨
Application.ScreenUpdating = booScreenUpdating

End Sub

' _____________________________________________________________________________
' WORKBOOK OPEN
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' Descrip: Every time we save the workbook, we hide all the sheets but the
' splash sheet.
'
' Args: n/a
'
' Returns: n/a
'
' Date Developer Comments
' ¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨
' 01 Jan 2014 G. Truby • initial version
' _____________________________________________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Private Sub Workbook_Open()
' _____________________________________________________________________________

If Not fnHideSplashAndShowNormalSheets Then

ErrMsgBox errFME_BadWBProtectPasswordAtOpen, _
vbCritical

End If

'// we've made changes to the workbook by hiding & unhiding
'// sheets. but we haven't actually changed any cell values
'// therefore if we were to just close the workbook now
'// we wouldn't want to prompt the user to save changes
'// he hasn't made any himself, only our code has. so it
'// would be okay to just exit without saving the changes we made.
Let Me.Saved = True

End Sub


'______________________________________________________________________________
' · · · e n d o f m o d u l e · · ·
'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯

The “Force Macros Enabled” Module
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' «| GENERIC [MULTI-PROJECT] |»
' «| "Force" the User to Enable Macros |»
' _____________________________________________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' module: basXL_ForceMacrosEnabled
'
' date: Jan 2014
'
' author: Greg Truby | www.gabraria.com | www.excelplaza.com
'
' summary: This module contains code that will hide all the worksheets
' except a "splash screen" that instructs the user to enable
' macros. If the user enabled macros, he does not see the
' screen. If he does not enable macros he will only see the
' splash screen.
'
' This code should have come from the following URL:
'
' http://www.excelplaza.com/ep_truby/Article_002_Forcing_Macros_Enabled_Part1
' /truby_002_force_macros_enabled_p1.php
'
' Please leave the above URL in this module's comments in
' case I ever need to issue revisions to the code base to
' fix any bugs or improve robustness.
'
' Concepts and/or code have been borrowed from one or more
' of the following URLs:
'
' http://www.cpearson.com/excel/EnableMacros.aspx
' http://www.xl-central.com/force-users-to-enable-macros-in-a-workbook.html
' http://www.excelguru.ca/content.php?162-Force-User-To-Enable-Macros
' http://datapigtechnologies.com/blog/index.php/forcing-your-clients-to-enable-macros/
' _____________________________________________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' copyright / work-for-hire status: [public/open]
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯ ¨¨¨¨¨¨¨¨¨¨¨¨¨
' The code in this module is either taken entirely from public sources on the
' internet or is a combination of public sources with some enhancements made
' by GabrAria LLC or code written by GabrAria with the intention of making the
' code publicly available on the internet at a future date.
' Therefore none of the code in this module is copyrighted by GabrAria LLC
' and to GabrAria's knowledge it is not copyrighted by any other author(s).
' _____________________________________________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' • see module basZZ_ProjectNotes for comments specific to this project
' • see module basZZ_GeneralCodingNotes for comments regarding general programming
' and coding conventions used.
' _____________________________________________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' contents:
' ¯¯¯¯¯¯¯¯¯
' Func fnHideSplashAndShowNormalSheets
' Func fnRecordStateAndHide
' Func fnSaveThisWorkbook
' Func fnSheetByCodeName
' Func fnSheetExistsByCodeName
' Func fnUnion
' Func fnVisibleWorkbookCount
' Func udfSheetNameFromCodeName
' Sub EverybodyOutWhereICanSeeYou
' _____________________________________________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Option Explicit
Option Private Module


'··············································································
' • • • G L O B A L - L E V E L D E C L A R A T I O N S • • •
'··············································································
'// if using a separate module for globals, move these declarations
'// to that module.
Public Const gc_booDebugMode As Boolean = True
Public Const gc_strAppName As String = "Force Macros Enabled"


'··············································································
' • • • M O D U L E - L E V E L D E C L A R A T I O N S • • •
'··············································································

'// __constants_________
'// ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
'// __strings___________

Private Const mc_strThisModule As String = "basXL_ForceMacrosEnabled_Greg" '// for error logging


'// These constants should only be changed if you are 100% sure of
'// what you're doing! They are the CODE NAME of the worksheet that
'// serves as the visibilites map and the names of the tables and ranges
'// that are key to the correct operation of the code that hides/unhides
'// the sheets. Note that for the password, it is NAME on the VisibilitesMap
'// worksheet that contains the password to unlock the workbook structure
'// (if the workbook is indeed password protected). The constant below is
'// NOT the password itself. The approach allows us to prompt for the password if
'// the user suddenly decides to engage workbook structure protections when
'// we hadn't done so originally.
Private Const mc_strCodeName_VisMap As String = "wksVH_Visibilities", _
mc_strTable_Map As String = "tbl_FME_VisibilitiesMap", _
mc_strName_Password As String = "ptrProtectionPswd"

'// ____________________
'// ¯¯constants¯¯¯¯¯¯¯¯¯


'¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨

'// __enumerations______
'// ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯

Private Enum me_MapColumns
eMapColCodeName = 1 ' A
eMapColSheetName ' B
eMapColCanDelete ' C
eMapColDefaultVis ' D
eMapColDefVisCode ' E
eMapColLastVisCode ' F
eMapColLastVis ' G
eMapColCount = 7 ' count
End Enum

Private Enum me_Visibility
eVisVisible = -1 ' -1
eVisHidden ' 0
eVisVeryHidden = 2 ' 2
eVisActive = 97 ' 97
eVisSplash ' 98
eVisNoDefault ' 99
End Enum

'// ____________________
'// ¯¯enumerations¯¯¯¯¯¯

'··············································································
' · · · e n d o f m o d u l e - l e v e l d e c l a r a t i o n s · · ·
'••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••

' ____________________________________________________________________________
' FN HIDE SPLASH AND SHOW NORMAL SHEETS
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' Descrip: Done when the workbook opens (if macros are enabled). Change
' the visibility of the "splash" sheet to xlVeryHidden and the
' visibilities of the rest of the sheets to whatever is indicated
' in a table on a worksheet that drives visibilities.
'
' Args: n/a
'
' Returns: Boolean · · · · · · TRUE if no errors were hit. Most likely
' error would be if workbook structure is protected
' and the password in the visibilities map is
' incorrect and the user cannot provide the correct
' password.
'
' Date Developer Comments
' ¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨
' 31 Dec 2013 G. Truby • initial version
' _____________________________________________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Function fnHideSplashAndShowNormalSheets() As Boolean
' _____________________________________________________________________________

Const c_strThisProc As String = "HideSplashAndShowNormalSheets" '// used by error logging
Dim lngErrNbr As Long, _
strErrMsg As String
On Error GoTo HideSplashAndShowNormalSheets_Error
'··········································································

Dim booHasProtectStructure As Boolean, _
booHasProtectWindows As Boolean, _
booScreenUpdating As Boolean, _
celCodeName As Excel.Range, _
celPassword As Excel.Range, _
celVisDef As Excel.Range, _
celVisLast As Excel.Range, _
intOffset As Integer, _
intResponse As Integer, _
intStartRow As Integer, _
intVisDef As Integer, _
intVisLast As Integer, _
rngColCodeNames As Excel.Range, _
rngColDefVis As Excel.Range, _
rngColLastVis As Excel.Range, _
rngMap As Excel.Range, _
shtActive As Object, _
shtItem As Object, _
strPassword As String, _
wsMap As Excel.Worksheet, _
wsSplash As Excel.Worksheet

'// we're doing this first because our clean up code
'// restores this setting. which means that this setting
'// should be known before any possible error might
'// be raised and cause us to hit our clean up code
'// sooner than we expect.
With Application
Let booScreenUpdating = .ScreenUpdating
.ScreenUpdating = False
End With

Let fnHideSplashAndShowNormalSheets = False '// default return value

Set wsMap = fnSheetByCodeName(mc_strCodeName_VisMap, ThisWorkbook)
If wsMap Is Nothing Then
ErrMsgBox ge_ErrorMessages.errFME_VisibilitiesMapMissing, _
vbExclamation, _
mc_strThisModule, _
mc_strCodeName_VisMap
GoTo HideSplashAndShowNormalSheets_EarlyExit
End If

'// get our map, then password
With wsMap
Set rngMap = .ListObjects(mc_strTable_Map).DataBodyRange
Set celPassword = .Range(mc_strName_Password)
End With
Let strPassword = celPassword.Value

'// use our password to unlock the workbook (if its structure
'// is protected).
With ThisWorkbook
Let booHasProtectStructure = .ProtectStructure
Let booHasProtectWindows = .ProtectWindows

UnProtectWorkbook:
'¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨
If booHasProtectStructure Then .Unprotect strPassword
End With

With rngMap
Set rngColCodeNames = .Columns(me_MapColumns.eMapColCodeName)
Set rngColDefVis = .Columns(me_MapColumns.eMapColDefVisCode)
Set rngColLastVis = .Columns(me_MapColumns.eMapColLastVisCode)
End With

'// we're going to calculate row differentials to find the
'// corresponding rows in other columns of our table
'// it make the code a bit easier to read & debug if
'// we capture the row number of the start of the data block(s).

Let intStartRow = rngColCodeNames.Range("A1").Row

'// need to locate the splash sheet right quick
For Each celCodeName In rngColCodeNames.Cells
Let intOffset = celCodeName.Row - intStartRow
Set celVisDef = rngColDefVis.Range("A1").Offset(intOffset)
If celVisDef.Value = me_Visibility.eVisSplash Then
Set wsSplash = fnSheetByCodeName(celCodeName.Value)
Exit For
End If
Next celCodeName

'// we're going to loop the collection of SHEETS, not WORKSHEETS
'// just in case there's a CHART sheet in the workbook.

For Each shtItem In ThisWorkbook.Sheets

If shtItem Is wsSplash Then GoTo EndOfSheetLoop ' |--¿goto?-->

Set celCodeName = rngColCodeNames.Find(What:=shtItem.CodeName, _
LookAt:=XlLookAt.xlWhole, _
LookIn:=XlFindLookIn.xlValues, _
MatchCase:=False)

If celCodeName Is Nothing Then

'// not in our table - set to visible
shtItem.Visible = XlSheetVisibility.xlSheetVisible

Else

'// in the table
Let intOffset = celCodeName.Row - intStartRow
Set celVisDef = rngColDefVis.Range("A1").Offset(intOffset)
Set celVisLast = rngColLastVis.Range("A1").Offset(intOffset)
Let intVisDef = celVisDef.Value
If Len(celVisLast.Value) <> 0 Then
Let intVisLast = celVisLast.Value
Else
Let intVisLast = XlSheetVisibility.xlSheetVisible
End If

Select Case intVisDef

'// splash sheet
Case me_Visibility.eVisSplash

shtItem.Visible = XlSheetVisibility.xlSheetVeryHidden

'// a default has been defined
Case me_Visibility.eVisHidden, _
me_Visibility.eVisVisible, _
me_Visibility.eVisVeryHidden

shtItem.Visible = intVisDef

Case me_Visibility.eVisNoDefault

If intVisLast <> me_Visibility.eVisActive Then
shtItem.Visible = intVisLast
Else
shtItem.Visible = XlSheetVisibility.xlSheetVisible
End If

Case Else

shtItem.Visible = XlSheetVisibility.xlSheetVisible

End Select

'// attempt to set the active sheet. It might be that the
'// user unhid a sheet with a default visibility of hidden
'// and this particular sheet was the active sheet at the time
'// the save was executed. Therefore we'll set wsActive to the
'// first visible sheet to act as a default and then if we
'// come across a sheet where last visibility was "active" and
'// that sheet's default visibility was visible or no-default
'// then we'll go ahead and set wsActive to that sheet.

If shtActive Is Nothing Then
If shtItem.Visible = XlSheetVisibility.xlSheetVisible Then
Set shtActive = shtItem
End If
Else
If intVisLast = me_Visibility.eVisActive _
And shtItem.Visible = XlSheetVisibility.xlSheetVisible Then
Set shtActive = shtItem
End If
End If

End If '// celCodeName is nothing

EndOfSheetLoop:
'¨¨¨¨¨¨¨¨¨¨¨¨¨¨
Next shtItem

shtActive.Activate
wsSplash.Visible = xlSheetVeryHidden

If booHasProtectStructure Then
ThisWorkbook.Protect strPassword, booHasProtectStructure, booHasProtectWindows
End If

Let fnHideSplashAndShowNormalSheets = True

HideSplashAndShowNormalSheets_EarlyExit:
'¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨
GoSub CleanUp
Exit Function

CleanUp:
'¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨
'// not a lot of cleaning up to do...
Application.ScreenUpdating = booScreenUpdating

Return

HideSplashAndShowNormalSheets_Error:
'¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨
'// use this section to pass any additional information to the
'// error handler for logging. If not needed then delete from the
'// IF to the END IF.
If Err.Number = 0 Then
Let strErrMsg = ""
End If

If Err.Number = 1004 Then

'// workbook IS protected but password is different than what
'// is on the visibilities map. try to get a correct password
'// and up date the map worksheet.
Let intResponse = ErrMsgBox(ge_ErrorMessages.errFME_BadWBProtectPassword, _
vbExclamation + vbOKCancel)

If intResponse = vbOK Then
Let strPassword = InputBox("What is the password to unlock the workbook's structure?", _
"Protection Password Needed")
celPassword.Formula = strPassword
Resume UnProtectWorkbook
Else
Resume HideSplashAndShowNormalSheets_EarlyExit
End If

End If

'// certain actions in the cleanup process (notably calling QPTimer_End) will
'// clear the error number, so store it before doing any cleanup actions
Let lngErrNbr = Err.Number

If Not gc_booDebugMode Then GoSub CleanUp

#If gccc_UseCentralErrorHandler Then
CentralErrorHandler lngErrNum:=lngErrNbr, _
strModule:=mc_strThisModule, _
strProc:=c_strThisProc, _
strExtraErrInfo:=strErrMsg ' , booEntryPoint:=True
#Else
If Len(strErrMsg) <> 0 Then
MsgBox strErrMsg, vbExclamation, "Error"
Else
MsgBox "Err #: " & Err.Number & vbCr _
& Err.Description, vbExclamation, "Error"
End If
#End If
If gc_booDebugMode Then
Stop
Resume
End If

End Function '// fn Hide Splash And Show Normal Sheets

' _____________________________________________________________________________
' FN RECORD STATE AND HIDE
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' Descrip: Use the table in the visibilities map worksheet to record the
' current visibilities of each sheet and which sheet is active.
'
' Args: n/a
'
' Returns: Boolean · · · · TRUE if we were able to record the current visibility
' states and hide all but the splash sheet; false if failed.
'
' Likely causes for failure: a. workbook structural protection being applied and the
' correct password is not on the visibilities map
' worksheet and the user cannot supply correct password.
' b. the user deleted a worksheet where the map indicates
' that the sheet cannot be deleted.
'
' Date Developer Comments
' ¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨
' 01 Jan 2014 G. Truby • initial version
' _____________________________________________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Function fnRecordStateAndHide() As Boolean
' _____________________________________________________________________________

Const c_strThisProc As String = "fnRecordStateAndHide" '// used by error logging
Dim lngErrNbr As Long, _
strErrMsg As String
On Error GoTo fnRecordStateAndHide_Error
'··········································································

Dim booCanDelete As Boolean, _
booHasProtectStructure As Boolean, _
booHasProtectWindows As Boolean, _
booScreenUpdating As Boolean, _
celCodeName As Excel.Range, _
celPassword As Excel.Range, _
intResponse As Integer, _
intRowIndex As Integer, _
intStartRow As Integer
Dim loVisTable As Excel.ListObject, _
rngColCodeNames As Excel.Range, _
rngMap As Excel.Range, _
rngThisRow As Excel.Range, _
rngToDelete As Excel.Range, _
shtItem As Object, _
strPassword As String, _
wsMap As Excel.Worksheet, _
wsSplash As Excel.Worksheet

'// we're doing this first because our clean up code
'// restores this setting. which means that this setting
'// should be known before any possible error might
'// be raised and cause us to hit our clean up code
'// sooner than we expect.
With Application
Let booScreenUpdating = .ScreenUpdating
.ScreenUpdating = False
End With

Let fnRecordStateAndHide = False '// default return value

Set wsMap = fnSheetByCodeName(mc_strCodeName_VisMap, ThisWorkbook)
If wsMap Is Nothing Then
ErrMsgBox ge_ErrorMessages.errFME_VisibilitiesMapMissing, _
vbExclamation, _
mc_strThisModule, _
mc_strCodeName_VisMap
GoTo fnRecordStateAndHide_EarlyExit
End If

'// get our map, then password
With wsMap
Set loVisTable = .ListObjects(mc_strTable_Map)
Set celPassword = .Range(mc_strName_Password)
End With
Set rngMap = loVisTable.DataBodyRange

Let strPassword = celPassword.Value

'// first things first: do we have any issues regarding
'// workbook structure protection? if we do and we can't
'// get them resolved, this party isn't going to go anywhere.
'// so let's use our password to unlock the workbook (if
'// its structure is protected).
With ThisWorkbook
Let booHasProtectStructure = .ProtectStructure
Let booHasProtectWindows = .ProtectWindows

UnProtectWorkbook:
'¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨
If booHasProtectStructure Then .Unprotect strPassword
End With

'// next we need to scan our table and see if there are rows we need
'// to delete or add because sheets have been deleted or added.
'// if a sheet was deleted where we have prohibited its deletion
'// then we'll send an error message and leave with a return
'// value of false.

'// ••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••
'// ·····························································
'// ····· scanning table looking for rows we need to delete ·····
'// ····· or sheets that are missing (deletion prohibited) ·····
'// ·····························································

Set rngColCodeNames = rngMap.Columns(me_MapColumns.eMapColCodeName)
Let intStartRow = rngColCodeNames.Range("A1").Row

'// (not really necessary, clearing contents to make it easier to read during development
'// and it fails under anything under XL2010, so using conditional compiling so that compiler
'// won't bellyache for XL2007 and below.)
#If VBA7 Then
loVisTable.ListColumns(me_MapColumns.eMapColLastVisCode).DataBodyRange.ClearContents
#End If

'// run down the list of codenames and make sure all the ones
'// that must exist, do exist. while we're at it, jot down
'// their visibility status

For Each celCodeName In rngColCodeNames.Cells

Let intRowIndex = celCodeName.Row - intStartRow + 1
Set rngThisRow = loVisTable.ListRows(intRowIndex).Range

Let booCanDelete = (LCase(rngThisRow(1, me_MapColumns.eMapColCanDelete)) <> "no")

If fnSheetExistsByCodeName(celCodeName.Value) Then
Set shtItem = fnSheetByCodeName(celCodeName)
If ActiveSheet Is shtItem Then
rngThisRow(1, me_MapColumns.eMapColLastVisCode).Formula = me_Visibility.eVisActive
Else
rngThisRow(1, me_MapColumns.eMapColLastVisCode).Formula = shtItem.Visible
End If

'// update the sheet name in the list just in case
'// our user changed it.
rngThisRow(1, me_MapColumns.eMapColSheetName).Formula = shtItem.Name

If rngThisRow(1, me_MapColumns.eMapColDefVisCode) = me_Visibility.eVisSplash Then
'// we've found our splash sheet, assign it to a variable
'// so we don't have to look for it again when we want to hide it.
Set wsSplash = shtItem
End If

Else

'// sheet doesn't exist, are we okay with that?
If booCanDelete Then
'// it's okay, so delete the table row
Set rngToDelete = fnUnion(rngToDelete, rngThisRow)
Else
'// we're not okay with this. tell the user they cannot
'// save and exit, returning a value of FALSE.
ErrMsgBox errFME_DeletedRequiredSheet, _
vbCritical, _
rngThisRow(1, me_MapColumns.eMapColSheetName).Value
GoTo fnRecordStateAndHide_EarlyExit ' |--¿goto?-->
End If

End If

Next celCodeName

'// there were rows in our table for which there is now no
'// corresponding sheet (and no error was raised). delete
'// these rows from the table.
If Not rngToDelete Is Nothing Then
rngToDelete.Delete Shift:=XlDeleteShiftDirection.xlShiftUp
End If


'// now we need to run through all the sheets and add rows for
'// any new sheets that are not listed in the table (the user
'// added them to the workbook).

'// ••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••
'// ·················································
'// ····· looping the sheets collection looking ·····
'// ····· for sheets we need to add to table ) ·····
'// ·················································

For Each shtItem In ThisWorkbook.Sheets

Set celCodeName = rngColCodeNames.Find(What:=shtItem.CodeName, _
LookAt:=XlLookAt.xlWhole, _
LookIn:=XlFindLookIn.xlValues, _
MatchCase:=False)

If celCodeName Is Nothing Then
Set rngThisRow = loVisTable.ListRows.Add.Range

With rngThisRow
.Cells(1, me_MapColumns.eMapColCodeName).Formula = shtItem.CodeName
.Cells(1, me_MapColumns.eMapColCanDelete).Formula = "Yes"
.Cells(1, me_MapColumns.eMapColSheetName).Formula = shtItem.Name
.Cells(1, me_MapColumns.eMapColDefVisCode).Formula = me_Visibility.eVisNoDefault
If shtItem Is ActiveSheet Then
.Cells(1, me_MapColumns.eMapColLastVisCode).Formula = me_Visibility.eVisActive
Else
.Cells(1, me_MapColumns.eMapColLastVisCode) = shtItem.Visible
End If
End With
End If

Next shtItem

'// ••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••
'// ·······················································
'// ····· now we run the collection one more time and ·····
'// ····· "very hide" all but the splash sheet ·····
'// ·······················································

wsSplash.Visible = xlSheetVisible

For Each shtItem In ThisWorkbook.Sheets

If Not shtItem Is wsSplash Then
shtItem.Visible = XlSheetVisibility.xlSheetVeryHidden
End If

Next shtItem

Let fnRecordStateAndHide = True

fnRecordStateAndHide_EarlyExit:
'¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨
GoSub CleanUp
Exit Function

CleanUp:
'¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨
'// not a lot of cleaning up to do...
Application.ScreenUpdating = booScreenUpdating

Return

fnRecordStateAndHide_Error:
'¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨
'// use this section to pass any additional information to the
'// error handler for logging. If not needed then delete from the
'// IF to the END IF.
If Err.Number = 0 Then
Let strErrMsg = ""
End If

If Err.Number = 1004 Then

'// workbook IS protected but password is different than what
'// is on the visibilities map. try to get a correct password
'// and up date the map worksheet.
Let intResponse = ErrMsgBox(ge_ErrorMessages.errFME_BadWBProtectPassword, _
vbExclamation + vbOKCancel)

If intResponse = vbOK Then
Let strPassword = InputBox("What is the password to unlock the workbook's structure?", _
"Protection Password Needed")
celPassword.Formula = strPassword
Resume UnProtectWorkbook
Else
Resume fnRecordStateAndHide_EarlyExit
End If

End If

'// certain actions in the cleanup process (notably calling QPTimer_End) will
'// clear the error number, so store it before doing any cleanup actions
Let lngErrNbr = Err.Number

If Not gc_booDebugMode Then GoSub CleanUp

#If gccc_UseCentralErrorHandler Then
CentralErrorHandler lngErrNum:=lngErrNbr, _
strModule:=mc_strThisModule, _
strProc:=c_strThisProc, _
strExtraErrInfo:=strErrMsg ' , booEntryPoint:=True
#Else
If Len(strErrMsg) <> 0 Then
MsgBox strErrMsg, vbExclamation, "Error"
Else
MsgBox "Err #: " & Err.Number & vbCr _
& Err.Description, vbExclamation, "Error"
End If
#End If
If gc_booDebugMode Then
Stop
Resume
End If

End Function '// fn Record State And Hide

' _____________________________________________________________________________
' FN SAVE THIS WORKBOOK
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' Descrip: We can't let the user save the file in the XLSX format which would
' strip out all our macros. Therefore we're only going to allow them
' to save using XLS ro XLSM file formats.
'
' Credit: I would be remiss if I did not mention Domenic Tamburino & Ken Puls.
' Much of the code you see here is based on work they originally published.
' See the notes at the top of this module for the URLs for their
' original articles.
'
' Args: booSaveAsUI · · TRUE if the user is going a SAVEAS instead of a normal
' SAVE which means the SaveAs User Interface is being
' used.
'
' Returns: Boolean · · · · TRUE if the file saved (the user may have canceled
' out of a SaveAs action).
'
' Date Developer Comments
' ¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨
' 02 Jan 2014 G. Truby • initial version
' _____________________________________________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Function fnSaveThisWorkbook(Optional ByVal booSaveAsUI As Boolean = False) As Boolean
' _____________________________________________________________________________

Const c_strThisProc As String = "fnSaveThisWorkbook" '// used by error logging
Dim lngErrNbr As Long, _
strErrMsg As String
On Error GoTo fnSaveThisWorkbook_Error
'··········································································

Dim booEventsEnabled As Boolean, _
booOrigSavedStatus As Boolean, _
intFilterIndex As Integer, _
lngFileFormat As Long, _
strFileFilter As String, _
vntFileName As Variant

With Application
Let booEventsEnabled = .EnableEvents

'// if we don't turn off events, when we save we'll
'// raise a BeforeSave event, which we don't want as
'// we're already doing this save from _beforeclose
'// or it would be a SECOND beforesave event if we're
'// here due to coming in from the BeforeSave event handler.
.EnableEvents = False
End With

Let booOrigSavedStatus = ThisWorkbook.Saved

If booSaveAsUI Or Len(ThisWorkbook.Path) = 0 Then

'// if XL2003 or lower, only allow XLS, otherwise XLS or XLSM
If Val(Application.Version) < 12 Then
strFileFilter = "Microsoft Office Excel Workbook (*.xls), *.xls"
intFilterIndex = 1
Else
strFileFilter = "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm, " _
& "Excel 97-2003 Workbook (*.xls), *.xls"
If Right(ThisWorkbook.Name, 4) = ".xls" Then
intFilterIndex = 2
Else
intFilterIndex = 1
End If
End If

Do
Let vntFileName = Application.GetSaveAsFilename(InitialFileName:=ThisWorkbook.Name, _
FileFilter:=strFileFilter, _
FilterIndex:=intFilterIndex, _
Title:="Save As", _
ButtonText:="Save As")

'// user hit Cancel from the GetSaveAsFilename Dialog box
If TypeName(vntFileName) = "Boolean" Then
Let fnSaveThisWorkbook = booOrigSavedStatus
GoTo fnSaveThisWorkbook_EarlyExit
End If

'// note - we do not need to verify that the path exists or that
'// the file name is a legal file name. The GetSaveAsFilename dialog box
'// will not accept an illegal file name nor a non-existent path.

If CInt(Application.Version) < 12 Then
Let lngFileFormat = XlFileFormat.xlWorkbookNormal '// -4143
Else
'// we cannot use the constants XlFileFormat.xlExcel8 (=56) nor
'// XlFileFormat.xlOpenXMLWorkbookMacroEnabled if we are to be
'// able to run under XL2003, because these constants do not exist
'// in XL2003.
If Right$(vntFileName, 4) = ".xls" Then
Let lngFileFormat = 56 '// =XlFileFormat.xlExcel8
Else
Let lngFileFormat = 52 '// =XlFileFormat.xlOpenXMLWorkbookMacroEnabled
End If
End If

On Error Resume Next
ThisWorkbook.SaveAs FileName:=vntFileName, _
FileFormat:=lngFileFormat

'// a 1004 error code would happen if file existed
'// and user decided not to overwrite.
If Err.Number <> 0 And Err.Number <> 1004 Then
ErrMsgBox errFME_ProblemBeforeSave, vbCritical
End If
If Err.Number <> 0 Then GoTo fnSaveThisWorkbook_EarlyExit ' |--¿goto?-->

On Error GoTo fnSaveThisWorkbook_Error
Application.RecentFiles.Add vntFileName

EndOfGetFileNameLoop:
'¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨
Loop Until ThisWorkbook.Saved

Else '// not doing a SaveAs, just a normal save
ThisWorkbook.Save
End If

Let fnSaveThisWorkbook = True


fnSaveThisWorkbook_EarlyExit:
'¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨
GoSub CleanUp
Exit Function

CleanUp:
'¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨

With Application
.EnableEvents = booEventsEnabled
End With

Return

fnSaveThisWorkbook_Error:
'¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨
'// use this section to pass any additional information to the
'// error handler for logging. If not needed then delete from the
'// IF to the END IF.
If Err.Number = 0 Then
Let strErrMsg = ""
End If

'// certain actions in the cleanup process (notably calling QPTimer_End) will
'// clear the error number, so store it before doing any cleanup actions
Let lngErrNbr = Err.Number

If Not gc_booDebugMode Then GoSub CleanUp

#If gccc_UseCentralErrorHandler Then
CentralErrorHandler lngErrNum:=lngErrNbr, _
strModule:=mc_strThisModule, _
strProc:=c_strThisProc, _
strExtraErrInfo:=strErrMsg ' , booEntryPoint:=True
#Else
If Len(strErrMsg) <> 0 Then
MsgBox strErrMsg, vbExclamation, "Error"
Else
MsgBox "Err #: " & Err.Number & vbCr _
& Err.Description, vbExclamation, "Error"
End If
#End If

If gc_booDebugMode Then
Stop
Resume
End If


End Function '// fn Save This Workbook


' _____________________________________________________________________________
' fn SHEET BY CODENAME
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' Descrip: Return a sheet object (worksheet or chart) based on its codename
' instead of its name.
'
' Args: strCodeName · · the codename of the sheet sought
' [wbSource]· · · the workbook object which should contain the sheet.
' if not specified then the activeworkbook is used.
'
' Returns: Object· · · · · the object with given codename (might be a chart
' sheet or might be a worksheet).
'
' Date Developer Comments
' ¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨
' 31 Dec 2013 G. Truby • initial version. based on a function many years
' old that would return a WORKsheet based on
' its codename called fnWorksheetByCodeName.
' _____________________________________________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Function fnSheetByCodeName(ByVal strCodeName As String, _
Optional ByRef wbSource As Excel.Workbook) _
As Object
' _____________________________________________________________________________

Dim booFound As Boolean, _
shtOne As Object

Let booFound = False

If wbSource Is Nothing Then
If fnVisibleWorkbookCount <> 0 Then
On Error Resume Next
Set wbSource = ActiveWorkbook
End If
End If

If wbSource Is Nothing Then GoTo SetAndLeave ' |--¿goto?-->

For Each shtOne In wbSource.Sheets

If StrComp(shtOne.CodeName, strCodeName, vbTextCompare) = 0 Then
Let booFound = True
Exit For ' |--¿xloop?-->
End If
Next shtOne

SetAndLeave:
'¨¨¨¨¨¨¨¨¨¨¨
If booFound Then
Set fnSheetByCodeName = shtOne
Else
Set fnSheetByCodeName = Nothing
End If

End Function

' _____________________________________________________________________________
' FN SHEET EXISTS BY CODE NAME
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' Descrip: The function name pretty much sums it up. But do note that this
' function tests for SHEETS, not only worksheets, i.e. it can
' check for the existence of a Chart sheet.
'
' Args: strCodeName · · the code name of the sheet object being checked.
' [wbSource]· · · the workbook where the sheet should be sought.
'
' Returns: Boolean · · · · TRUE if the sheet exists.
'
' Date Developer Comments
' ¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨
' 01 Jan 2014 G. Truby • initial version
' _____________________________________________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Function fnSheetExistsByCodeName(ByVal strCodeName As String, _
Optional ByRef wbSource As Excel.Workbook) As Boolean
' _____________________________________________________________________________

Dim shtTest As Object
Set shtTest = fnSheetByCodeName(strCodeName, wbSource)
Let fnSheetExistsByCodeName = Not shtTest Is Nothing
End Function


' _____________________________________________________________________________
' FN UNION
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' Descrip: If you attempt to build a range that is the union of ranges and any
' of the input range objects is nothing, Union() will fail. This routine
' checks first and then adds the range if it's not nothing.
'
' Args: Rngs()· · · · · a list of one or more range objects.
'
' Returns: Range · · · · · the union of the various ranges.
'
' Date Developer Comments
' ¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨
' 17 Aug 2007 T. Mehta • initial version
' & A. Pope • taken from here:
' http://www.dailydoseofexcel.com/archives/2007/08/17/
' two-new-range-functions-union-and-subtract/
' _____________________________________________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Function fnUnion(ParamArray Rngs()) As Excel.Range
' _____________________________________________________________________________

Dim rngUnion As Excel.Range, _
vntX As Variant

On Error GoTo ErrorHandler_fnUnion

For Each vntX In Rngs
If Not vntX Is Nothing Then
If TypeName(vntX) = "Range" Then
If rngUnion Is Nothing Then
Set rngUnion = vntX
Else
Set rngUnion = Union(rngUnion, vntX)
End If
End If
End If
Next

If Not rngUnion Is Nothing Then Set fnUnion = rngUnion

Exit Function

ErrorHandler_fnUnion:

Stop
Resume

End Function '// fnUnion


' _____________________________________________________________________________
' fn VISIBLE WORKBOOK COUNT
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' Descrip: Count the # of visible workbooks - if a workbook has multiple windows
' and some are hidden and some are visible, count the WB as visible.
'
' Args: booCountHidden* · · toggle to return the # of hidden worbooks instead
'
' Returns: Integer · · · · · · # of visible workbooks
'
' Date Developer Comments
' ¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨
' 15 Nov 2011 G. Truby • initial version
' _____________________________________________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Private Function fnVisibleWorkbookCount( _
Optional booCountHidden As Boolean = False) _
As Integer
' _____________________________________________________________________________

Dim wndo As Excel.Window, _
wb As Excel.Workbook, _
v%

For Each wb In Application.Workbooks
For Each wndo In wb.Windows
If wndo.Visible Then
Let v = v + 1
Exit For
End If
Next wndo
Next wb

Let fnVisibleWorkbookCount = IIf(booCountHidden, _
Application.Workbooks.Count - v, _
v)

End Function

' +---------------------------------------------------------------+
' | I used the UDF below initially, but once I began addressing |
' | the issue of handing cases where the user had deleted a sheet |
' | that was not supposed to be deleted, it became apparent that |
' | I needed to hard-input the names using VBA rather than using |
' | a UDF to supply them. However, this is a bit useful in other |
' | contexts, so going ahead and leaving the code in the workbook |
' | even though not using it any more. |
' | Greg T., Jan. 2014 |
' +---------------------------------------------------------------+

' _____________________________________________________________________________
' UDF SHEET NAME FROM CODE NAME
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' Descrip: Given a codename for a sheet, return that sheet's name.
'
' Args: strCodeName · · the code name of the sheet sought
'
' Returns: Variant · · · · if found = string: the sheet name
' if not found = a reference error
'
' Date Developer Comments
' ¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨
' 31 Dec 2013 G. Truby • initial version
' _____________________________________________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Public Function udfSheetNameFromCodeName(ByVal strCodeName As String) As Variant
' _____________________________________________________________________________

Dim shtItem As Object

Set shtItem = fnSheetByCodeName(strCodeName)

If shtItem Is Nothing Then
Let udfSheetNameFromCodeName = CVErr(XlCVError.xlErrRef)
Else
Let udfSheetNameFromCodeName = shtItem.Name
End If

End Function


' _____________________________________________________________________________
' EVERYBODY OUT WHERE I CAN SEE YOU
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' Descrip: Very quick and simple little utility that comes in handy during
' the development process when forcing users to enable macros.
'
' Args: n/a
'
' Returns: n/a
'
' Date Developer Comments
' ¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨
' 29 Dec 2013 G. Truby • initial version
' _____________________________________________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Sub EverybodyOutWhereICanSeeYou()
' _____________________________________________________________________________

Dim objSheet As Object
For Each objSheet In ThisWorkbook.Sheets
objSheet.Visible = xlSheetVisible
Next objSheet
End Sub


'______________________________________________________________________________
' · · · e n d o f m o d u l e · · ·
'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯

The Error Messenger Module
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' «| GENERIC [MULTI-PROJECT] |»
' «| Error Messenger |»
' _____________________________________________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' module: basXL_ErrorMessenger
'
' date: Jan 2011
'
' author: Greg Truby | www.gabraria.com | www.excelplaza.com
'
' summary: The centralized error handler (CEH) is good at processing true
' errors as well as those situations where one would like to raise a
' custom error. And while there is some built-in functionality for
' using the CEH to simply display a message it's a bit of overkill.
'
' Therefore this module provides a tool that is similar to
' the CEH but light-weight designed for use in those situation
' where one wishes to send the user a fairly lengthy error message
' but where the logic should continue inside the routine and not
' begin bubbling out through the call stack.
'
' Also, the ErrMsgBox routine allows the coder to embed fixed string
' that can then be swapped out at runtime. The embedded strings
' take the form:
' • «replace.1»
' • «replace.2»
' • «replace.3»
' _____________________________________________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' copyright / work-for-hire status: [public/open]
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯ ¨¨¨¨¨¨¨¨¨¨¨¨¨
' The code in this module is either taken entirely from public sources on the
' internet or is a combination of public sources with some enhancements made
' by GabrAria LLC or code written by GabrAria with the intention of making the
' code publicly available on the internet at a future date.
' Therefore none of the code in this module is copyrighted by GabrAria LLC
' and to GabrAria's knowledge it is not copyrighted by any other author(s).
' _____________________________________________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' • see module basZZ_ProjectNotes for comments specific to this project
' • see module basZZ_GeneralCodingNotes for comments regarding general programming
' and coding conventions used.
' _____________________________________________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' contents:
' ¯¯¯¯¯¯¯¯¯
' Sub ErrMsgBox
' _____________________________________________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Option Explicit

'// __constants_________
'// ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
'// __strings___________

'Private Const mc_strThisModule As String = "basXL_ErrorMessenger" '// for error logging


Public Enum ge_ErrorMessages
errFME_BadWBProtectPassword
errFME_BadWBProtectPasswordAtOpen
errFME_DeletedRequiredSheet
errFME_ProblemAfterSave
errFME_ProblemAtClose
errFME_ProblemBeforeSave
errFME_VisibilitiesMapMissing
End Enum

' _____________________________________________________________________________
' ERR MSG BOX
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' Descrip: See comments in module header section for more info
'
' Args: eError· · · · · · · an enumerated constant listed above
' lngMsgBoxStyle· · · what type of box to display, used VB msg box constants
' vntSubStrings · · · optional ParamArray of strings that will be
' replaced within the text that is displayed
'
' Returns: vbMsgBoxResult· · · functions like a message box and returns the
' same type of constants
'
' Date Developer Comments
' ¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨
' 01 Jan 2011 G. Truby • initial version
' _____________________________________________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Public Function ErrMsgBox(ByVal eError As ge_ErrorMessages, _
ByVal lngMsgBoxStyle As VbMsgBoxStyle, _
ParamArray vntSubStrings() As Variant) As VbMsgBoxResult
' _____________________________________________________________________________

Const vbCr2 As String = vbCr & vbCr

Dim strMsg As String, _
i%, s$

Select Case eError

Case ge_ErrorMessages.errFME_BadWBProtectPassword

Let strMsg = "The structure of this workbook has been password protected " _
& "with an unknown password." & vbCr2 _
& "• Click OK to supply the needed password." & vbCr _
& "• Click CANCEL if you do not know the password." & vbCr2 _
& "Please note that you will not be able to use this workbook " _
& "until this issue is resolved."

Case ge_ErrorMessages.errFME_BadWBProtectPasswordAtOpen

Let strMsg = "Because the password on file is not unlocking the structure " _
& "of this workbook and you have been unable to provide the " _
& "needed password, you will need to contact whomover applied the " _
& "password protection to the workbook's structure and obtain " _
& "the required password before you will be able to use the workbook."

Case ge_ErrorMessages.errFME_DeletedRequiredSheet

Let strMsg = "A required sheet has been deleted from the workbook." & vbCr2 _
& "You will not be allowed to save with workbook with this " _
& "sheet missing. The last time the file was saved the missing " _
& "sheet's name was:" & vbCr2 _
& "• «replace.1»"

Case ge_ErrorMessages.errFME_ProblemAfterSave

Let strMsg = "The workbook encountered an error after doing the SAVE and was " _
& "unable to finish restoring sheet visibilities. Please get help " _
& "to resolve this issue." & vbCr2 & "Please note that the workbook's " _
& "SAVE should have worked properly and your worksheets, though not " _
& "visible, should be safe."

Case ge_ErrorMessages.errFME_ProblemAtClose

Let strMsg = "There was a problem during the closing process, you may attempt " _
& "to save the file by hitting Ctrl+S and then try closing again." _
& vbCr2 & "Or you may need to get some assistance with this workbook " _
& "in order to close and save your changes."

Case ge_ErrorMessages.errFME_ProblemBeforeSave

Let strMsg = "There was a problem during the saving process that has prevented " _
& "the Save from happening. If the workbook structure has been password " _
& "protected, then you need to make sure you know the correct password " _
& "to unlock the structure." & vbCr2 _
& "If this is not the problem you may need to copy sheets or copy and paste " _
& "data into another workbook to save your work until you can get help " _
& "resolving the problem that is stopping the save process."

Case ge_ErrorMessages.errFME_VisibilitiesMapMissing

Let strMsg = "Coding Error." & vbCr2 & "There is no worksheet with a code name that " _
& "corresponds to the constant that was defined at the top of the module." _
& vbCr2 & "module: " & vbTab & vbTab & "«replace.1»" _
& vbCr & "codename: " & vbTab & "«replace.2»" & vbCr2 _
& "Please edit the constant or the code name of the Visibilities Map worksheet."

End Select

If UBound(vntSubStrings) <> -1 Then
For i = LBound(vntSubStrings) To UBound(vntSubStrings)
Let s = "«replace." & i + 1 & "»"
Let strMsg = Replace(strMsg, s, vntSubStrings(i), 1, -1, vbTextCompare)
Next i
End If

Let ErrMsgBox = MsgBox(strMsg, lngMsgBoxStyle, gc_strAppName)

End Function

'______________________________________________________________________________
' end of module 
'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Code Blocks

Greg — View GT’s VBA Workbook Module Code as Text File


Greg — View GT’s VBA Standard Code Module for Forcing Macros Enabled as Text File


Greg — View GT’s VBA Standard Code Module for Error Messenging as Text File


Downloadable Workbook

Greg — Download GT’s “Force Macros Enabled” Workbook

So there you have it! A system for forcing users to enable macros that has all of our desired design attributes:

  • Traps the Workbook Before Save Event
  • Traps the Workbook Before Close Event
  • Does Not Force User to Save Unwanted Changes
  • Blocks User from Saving as XLSX File Format
  • Will Not Fail if User Renames the Splash Sheet
  • Allows Sheets Other Than Splash to Be Hidden or Very Hidden
  • Can Handle Workbooks with Structure/Windows Protection
  • Hides All Sheets, Not Just WORKsheets
  • Are the HideAllSheets/ShowAllSheets Macros Visible From Macro Dialog?
  • Suppresses Screen Updating While Hiding/Unhiding
  • Restores Workbook’s Active Sheet After Saving

Whew! I don’t know about you, but I’m plumb tuckered out. That was a pretty long bit of coding just to make sure folks user our macros when we give them a macro-enabled workbook. I think it’s time for a coffee break. And after the break we’ll take a look at the next two aspects I wish to address on this theme:

  • What should our splash sheet look like?
  • Take this whole thing a step further and see if we can automate the process.
Excel Plaza Logo

Greg Truby, January 2014


Top