"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.

 

[headtoadie]

As long as the user can open a new workbook, they can run this to get around it.

Sub un_lock()
Dim wb As Workbook
Dim ws As Worksheet
    For Each wb In Application.Workbooks
        For Each ws In ActiveWorkbook.Worksheets
            ws.Visible = xlSheetVisible
        Next
    Next
End Sub

Generally, If I am putting in limitations like you have created it is because I am trying to guide the user interaction. The best way to keep sensitive data out of the wrong hands is to never give them access to it in the first place. :wink:

-HT

 

[parry]

Hi Greg, I recently had the need to ensure macros were enabled in a project. I built a form to control input for validation purposes and because it was easier to enter via a form than the sheet directly. I was effectively using Excel as a database because Access wasnt available to all users.

The best way I found was to have 2 workbooks. One with the code and GUI (userform) and the other to store/retrieve data from. That way the book with the data can be protected and the user must enable macros on the code book in order for the button to start the form to work. Easy peasy.

All depends what you are using the data for. Separating into 2 books has another advantage that if there are code changes required then the code book can merely be replaced for all users rather than having to change the code on individual books.

hth

 

[Legacy 1363]

All the user has to do to avoid enabling macros is to set EnableEvents to False after opening the workbook.

And then the user could open and close it as much as he likes in the same Excel session without having macros enabled.

 

[Greg Truby]

Firstly, thanks to HT, Parry & COJones for your input.

HT & CO - good feedback. In my particular case I am not hiding sheets for security or privacy. Here's how I got here...<ul>[*]Very complex workbook - real humdingers of formulas, UDF's, Event handlers intercepting changes on key cells to update data summaries and interactive charts, UserForms,... basically everything but the kitchen sink is in this one.[*]Show boss all this nifty stuff, he's suitably impressed.[*]E-mail spreadsheet to boss on Friday afternoon.[*]Leave work, go to gym, had good weekend.[*]Boss asks me to come over to his office on Monday morning.[*]Seems he was trying to show all of this fancy stuff to our VP Friday evening after I'd gone home and it wasn't working at all.[*]Had boss open WB.[*]He opens and of course just clicks the "Disable macros" button out of habit...[/list]And so that's how I got here. So the "holes" I'm looking for are not "holes in a security perimeter", they're "potholes in the road", i.e. ways that my users could inadvertantly through sheet bad luck screw this up. I'm not trying to prevent deliberate mischief done by "malice", I'm trying to prevent future reports of "your workbook no workie". Sorry if I misled.

Parry, great idea on segregating data & interface. I've already done that to a certain extent in this application. This particular workbook carries only summary data and gets sent to management here, other US sites and half-dozen overseas affiliates. I keep all of the original source data on my notebook. But if I have a need to do something along the lines of what you describe I will definitely keep that structure in mind when I am in the design phase of the project.

 

[Oorang]

Parry you can at least complicate their lives by protecting your workbook structure. (Unprotect at the beginning of the code, reportect at the end of the code.) That will at least force the whoever is nosing around to also crack the protection password before uniding sheets. (Admittadly not difficult, but it does makes it MORE difficult.)

 

[Anax]

Parry you can at least complicate their lives by protecting your workbook structure. (Unprotect at the beginning of the code, reportect at the end of the code.) That will at least force the whoever is nosing around to also crack the protection password before uniding sheets. (Admittadly not difficult, but it does makes it MORE difficult.)

Hi, I'm using dk's routine to hide/unhide sheets. But all my sheets are protected and when the hide and unhide functions trying to do their job I get the following error:

Run-time error '1004':
Unable to set the Visible property of the Workbook class

I get this although I unprotect the sheets before hide/unhide

Any Ideas on this?
My workbook is 3MB, I'm on XP with Office 2003

By the way, dk's routine works well on a small project with all sheets unproteted.

 

[Greg Truby]

Is the workbook's structure protected?

 

[Anax]

Is the workbook's structure protected?

Yes it is!

The code for this is the following:
<code>
ThisWorkbook.Protect password:="password", Structure:=True, Windows:=False
</code>
and the code for the sheet(s) protection is:
<code>
Sheet1.Protect password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True, UserInterfaceOnly:=False
</code>

 

[Oorang]

Unprotect it before you run the code or the code won't work.

ActiveWorkBook.Unprotect