Option Explicit
Dim bolInProcess As Boolean, _
bolClosing As Boolean
'//*************************************************************************************//
'// //
'// NOTE1: //
'// I have seen several different examples of how to force the user into having //
'// macros enabled. //
'// Unfortunately, these seem to either: //
'// //
'// 1. Force the user to save upon exiting, as BeforeClose has a Save in it. //
'// While this does more insist that the wb ends up closed with the desired //
'// sheets hidden (and the file is saved, thus opening w/macs disabled should //
'// always result in these sheets being hidden) - there is a price to pay, //
'// in that the user cannot open the wb in a usable state (macros enabled), //
'// make a change, and then decide that he errored and close the wb w/o saving. //
'// Thus - the bad data entered is saved... (Booo!) //
'// //
'// 2. The other manner I've seen this worked out is use the BeforeClose event, //
'// and IF the file is in a saved state when closing, hide the sheets and //
'// resave the wb. This works if the user opens wb, makes changes, saves, then //
'// closes wb. It also works if user opens wb, does or doesn't make changes, //
'// and closes w/o savong. But... If user opens wb, makes changes, saves, //
'// then makes more changes and exits w/o saving... well, the last save //
'// occurred with all the sheets exposed, so you can re-open w/macs disabled //
'// and there's the sheets (eeks!) you were trying to keep hidden. Nuts, eh? //
'// //
'// 3. The only method that I have seen work reliably is to create an //
'// "Workbook_AfterSave" event by using the BeforeSave event. In this manner, //
'// the file is never saved with the sheets displayed. Rather, at every Save, //
'// we first hide all sheets except a warning sheet, then we Save, then we //
'// redisplay the sheets the user is allowed to view and/or edit IF the user //
'// has enabled macros.
'// //
'// While the basis for this solution was primarily developed by Scott Dennison //
'// (Demosthine @ vbaexpress) along with some 'glitch-catching' by me; as the Bible //
'// says (Song of Solomon I believe), there is nothing new under the sun - and I //
'// later found very nice/same concept examples by Ken Puls: //
'// //
'// [URL]http://www.vbaexpress.com/kb/getarticle.php?kb_id=379[/URL] //
'// [URL]http://www.excelguru.ca/node/91[/URL] //
'// //
'// NOTE: For this example, we are making a three-sheet workbook. One sheet will //
'// be the warning sheet (shtForceEnable), which the user will only see if //
'// he opens the wb w/macros disabled. The other two sheets will only be //
'// seen if macros are enabled and will not be editable. //
'// ************************************************************************************//
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim intResponse As Integer
'// First, we'll kill alerts to prevent the application asking if we want to save //
'// (if wb not saved). We want to replace this with out own 'alert', so that we //
'// can control the save. //
Application.DisplayAlerts = False
'// bolClosing will be false by default, so we will initially pass this test. //
If Not bolClosing Then
'// IF the workbook is saved, it will close without further ado. ELSE, we will //
'// see what the user wants to do and control the results. //
If Not ThisWorkbook.Saved Then
intResponse = MsgBox("Do you want to save the changes you made to '" & _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & "'?", _
vbExclamation + vbYesNoCancel + vbDefaultButton1, _
"Discontinued List")
Select Case intResponse
Case vbYes
'// bolClosing used in BeforeSave//
bolClosing = True
'// See function. We don't need to execute the save here, and in //
'// fact, due to a weird glitch in Excel (least 2000/2003), this is //
'// better. //
Call Workbook_BeforeSave(False, False)
'//This is required, as even though the file saved while in //
'// BeforeSave, changes occurred post save. //
ThisWorkbook.Saved = True
Case vbNo
bolClosing = True
'// User doesn't want to save changes, so just mark file saved. //
'// This allows us to turn alerts back on and the file to close w/o //
'// alerting. //
ThisWorkbook.Saved = True
Case vbCancel
'// User cancelled closing, and least in Excel 2000, I found it //
'// necessary to reactivate stuff if I wanted the focus returned. //
ThisWorkbook.Activate
bolClosing = False
Cancel = True
Application.DisplayAlerts = True
ActiveCell.Activate
Exit Sub
End Select
End If
End If
Application.DisplayAlerts = True
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim bolStatusBarIsDisplayedSetting As Boolean, _
intCalculationSetting As Integer, _
wksWorksheet As Worksheet, _
wksLastActive As Worksheet, _
strSaveAs_Filename As String
If Not (bolInProcess _
And Not Cancel) Then
'// Set bolProcess to True, which will later prevent a recurse to this IF. //
bolInProcess = True
'// Let's see what the user's choices were, as to a few settings that we wish //
'// to temporarily control. //
bolStatusBarIsDisplayedSetting = Application.DisplayStatusBar
intCalculationSetting = Application.Calculation
'// Now we'll get rid of StatusBar (as on slower machines it seems to flicker //
'// even w/SreenUpdating False), and we'll stop screen updates and uneccessary //
'// calculations, which will give a better appearance and increase run speed. //
'Application.DisplayStatusBar = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
DoEvents
'// Find the ActiveSheet, so we can redisplay it later. //
Set wksLastActive = ThisWorkbook.ActiveSheet
'// Display the prompt (the warning) sheet BEFORE hidng remaining sheets. //
shtForceEnable.Visible = xlSheetVisible
'// In this example, I only have three sheets. A 'macwarn' sheet that //
'// should only display if user opens wb w/macs disabled, and two user //
'// sheets that should only show if macs are enabled. //
For Each wksWorksheet In ThisWorkbook.Worksheets
If Not wksWorksheet.CodeName = "shtForceEnable" Then
wksWorksheet.Visible = xlSheetVeryHidden
End If
Next
'// Note: For above - in larger projects, I would think of prefacing ea //
'// sheet's codename w/ 'shtV' and 'shtH' for visible/hidden sheets and //
'// using LIKE in the loop to determine whether to hide or unhide as the //
'// case may be. //
'// Set Cancel to True! If the user executed a "normal" save (clicking Save //
'// button or Ctrl+s...), then only the user called Save is cancelled. A //
'// couple of lines down, we'll execute a Save that will not be cancelled. //
'// If on the other hand, BeforeSave is called programatically, the Cancel does //
'// nothing, and the .Save (or SaveAs) executes under our control. //
'// I would also note that excluding the Cancel results in Saving the file //
'// twice. //
Cancel = True
DoEvents
'// Now, Save workbook. If you step-thru this, you'll see that it immedietely //
'// recurses to Workbook_BeforeSave, but as bolInProcess is currently True, //
'// there is no true recurse. Thus - the statements below the .Save execute, //
'// and the user sheets are redisplayed. //
'// //
'// The critical point here is that the workbook is never in a saved state with //
'// the user sheets visible! //
'// //
'// OK - let's determine whether the user is attempting a saveas. Depending //
'// upon the level of protection desired (file replication, etc), we could //
'// simply cancel the operation - or - if we want to allow a SaveAs, we want //
'// to do the following. //
If SaveAsUI Then
ChDir ThisWorkbook.Path
strSaveAs_Filename = Application.GetSaveAsFilename(InitialFileName:=ThisWorkbook.FullName, _
FileFilter:="Excel Files (*.xlsm), *.xlsm", _
Title:="Are you sure you want to SaveAs?")
'// Check to see if user cancelled; in which case reset bolInProcess and //
'// allow sheets to be redisplayed. Nothing is saved, so no harm, no foul. //
If strSaveAs_Filename = "False" Then
bolInProcess = False
Else
'// If user chose a filename and committed to the saveas, I strip the //
'// proposed name from the proposed fullname, so that we can saveas to //
'// the same folder we opened this workbook in. //
strSaveAs_Filename = "\" & Right(strSaveAs_Filename, _
Len(strSaveAs_Filename) _
- InStrRev(strSaveAs_Filename, "\", -1, _
vbTextCompare))
'// One last check. If user selects same name as current, then cancels //
'// the app alert msg (are your sure? there's already a workbook with //
'// this name...), an error ensues. Just skip past this error, and //
'// file will not be saved. //
On Error Resume Next
ThisWorkbook.SaveAs ThisWorkbook.Path & strSaveAs_Filename
Err.Clear
On Error GoTo 0
End If
Else
ThisWorkbook.Save
End If
'// IF we were closing the workbook when we decided to save, we'll leave //
'// everything hidden, reset settings, turn screen updating back on, //
'// and let the workbook close. //
If bolClosing = True Then
Application.DisplayStatusBar = bolStatusBarIsDisplayedSetting
Application.Calculation = intCalculationSetting
Application.ScreenUpdating = True
Exit Sub
End If
'// This is where we effectively created the "AfterSave" event/workaround. //
'// AFTER saving, we redisplay the sheet(s) that we want the user to be able to //
'// access. //
'// Redisplay whichever sheets you want the user to be able to see. //
For Each wksWorksheet In ThisWorkbook.Worksheets
wksWorksheet.Visible = xlSheetVisible
Next
'// THEN hide Prompt sheet AFTER redisplaying desired Worksheets. //
shtForceEnable.Visible = xlSheetVeryHidden
'// Oops! When we redisplayed sheets, we ended up inadvertantly selecting //
'// whatever sheet became visible in the last rotation thru the loop; so, //
'// let's select the sheet the user was viewing prior to the save. //
If Not ActiveSheet.Name = wksLastActive.Name Then
wksLastActive.Select
End If
'// You could also scroll to the last sheet, or where you want to be. This does //
'// not effect which sheet is actually selected; it just puts which tabs you want //
'// in view. //
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
'// Finally - we'll ensure that Excel controls the StatusBar text, updates/refreshs //
'// the screen, and tells Excel that everything is saved. //
'// Now, since we told Excel that it was saved, if the user then decides to close //
'// (and hasn't made any changes), the user will not be asked to save. On the //
'// other hand, if the user makes more changes, then he will be asked if he wants //
'// to save. If he... //
'// chooses yes: this routine will again be run, so that the workbook is //
'// again saved while all the sheets (except the sheet that //
'// advises that macros must be enabled) are again hidden, and //
'// our faux "AfterSave" will again display the sheets you //
'// wanted to be accessable will redisplay. //
'// //
'// chooses no: The workbook will close w/o saving. //
'// As every time the workbook is saved while macros are //
'// enabled, only the "MacWarn" sheet is left visible, AND, //
'// anytime that the workbook is opened with macros disabled //
'// and it is saved, it is saved with only the "MacroWarn" //
'// sheet displayed - there is now no easy way for the user //
'// to not allow the macros to run and use the workbook! //
Application.StatusBar = False
Application.DisplayStatusBar = bolStatusBarIsDisplayedSetting
Application.Calculation = intCalculationSetting
Application.ScreenUpdating = True
ThisWorkbook.Saved = True
'// Reset bolInProcess for the next time that user saves file. //
bolInProcess = False
End If
End Sub
Private Sub Workbook_Open()
Dim wksWorksheet As Worksheet
'// Redisplay whichever sheets you want the user to be able to see. //
For Each wksWorksheet In ThisWorkbook.Worksheets
wksWorksheet.Visible = xlSheetVisible
ActiveWorkbook.Sheets("Discontinued").Activate
Next
'// then hide Prompt sheet AFTER redisplaying desired Worksheets. //
shtForceEnable.Visible = xlSheetVeryHidden
'// Mark workbook as saved, in case the user decides to close w/o making any //
'// changes. This way, if the user opens and closes the workbook w/o making any //
'// changes, he isn't uneccessarily asked about saving changes. //
ThisWorkbook.Saved = True
End Sub
Public Sub Workbook_CallSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'// I have always noted a weird glitch, at least when //
'// run in OS:XP / Excel: 2000/2003. //
'// //
'// In short, the 'force enable' workaround functions correctly when the workbook //
'// is saved 'normally' by the user; that is - the user presses the save button or //
'// keys in the CTRL + s shortcut key combination. //
'// //
'// However, if the running program initiates the save (let's say it runs into a //
'// ThisWorkbook.Save), then if you follow (step-through) the code, you will see //
'// that rather than immedietely jumping back to line 63 (the beginning of //
'// 'Workbook_BeforeSave') as it should, it just continues and doesn't //
'// actually save. I have no explanation for this... //
'// //
'// Thus - either through changing 'Workbook_BeforeSave' to a public sub, or, by //
'// adding a procedure such as this, and then substituting a call such as: //
'// 'ThisWorkbook.Workbook_CallSave False, False' for ThisWorkbook.Save, I ensure //
'// that the workbook is actually saved. //
Call Workbook_BeforeSave(False, False)
End Sub
Glad to help 
