force enable macros & ASK to save changes (SOLUTION)

[erik.van.geit]

Hi all,

SUBJECT
How to force users to enable macros, using the "common method" (see below) AND still "ask" them if they want to save their changes or not!

PLEASE
This thread is meant to be a SOLUTION. Please only reply with useful enhancements (or requests which could lead to enhancements) and perhaps with links to similar webpages. Please do not reply with questions for your specific project: you can always start a new topic and link to here.


COMMON METHOD
Purpose
You want your users to enable macros for some reason.
Examples:
1. You have some access-restrictions and want to popup a userform when the workbook opens to enter Name & Password.
2. You wrote some code to print sheets with a certain layout.
Question:
How to force users to enable macros?.
Answer
The most common answer is:
Before close: Hide sheets (by macro) and display a "warning" sheet with a text like
"You need to enable macros to view this workbook!".
On open: show sheets again (and hide warningsheet)
This will only be effective if the workbook is saved while the sheets are hidden.
Remarks
We are trying to "keep out" the average user and will never be able to stop "professional *******s".
Daniel Klann used to be "the" reference for the above method, but it seems his site has expired.

PROBLEMS
1. The workbook will always be saved on close, even if we would not like it.
2. Often the "before-save"event is not used and a simple trick will then provide a workaround for malicious users.

SOLUTION
Purpose
1. Force users to enable macros by hiding the sheets when the workbook is closed.
2. Users can choose to save changes.
3. SaveAs is disabled.
STEPS
1. Create an extra sheet in your workbook. Call it "Macros Disabled"
write some text like
MACROS ARE DISABLED

It is not allowed to open this file with macros disabled.
The functionallity would not be optimal.

Please close the file and open it again enabling the macros.
If you didn't get the popup asking you to enable the macros, please proceed like this:
- go to menu Tools
- submenu Macro / Security
- set the protection level to "Medium"
- open this file again
2. Add this code to your project.
normal module

Option Explicit

Public bIsClosing As Boolean
Public bMadeChanges As Boolean

Sub HideSaveShow()
Dim CurSht As Worksheet

    With Application
    .EnableEvents = False
    .ScreenUpdating = False
    Set CurSht = ActiveSheet
    Call HideAll
    Call ShowAll
    CurSht.Activate
    .EnableEvents = True
    .ScreenUpdating = True
    End With

End Sub

Sub HideAll()
Dim sht As Worksheet

    With ThisWorkbook
    .Sheets("Macros Disabled").Visible = xlSheetVisible
    
        For Each sht In .Worksheets
        If sht.Name <> "Macros Disabled" Then sht.Visible = xlSheetVeryHidden
        Next sht
    
    .Save
    
    End With

End Sub

Sub ShowAll()
Dim sht As Worksheet

If bIsClosing Then Exit Sub

    With ThisWorkbook
    
        For Each sht In .Worksheets
        sht.Visible = xlSheetVisible
        Next sht

    .Sheets("Macros Disabled").Visible = xlSheetVeryHidden
    
    End With
    
    bMadeChanges = False
    
End Sub

In "ThisWorkbook"module

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If bIsClosing Then Exit Sub

Dim response As Integer

    With ThisWorkbook

        If bMadeChanges Then
        response = MsgBox("Save Changes?", vbYesNoCancel, "SAVE")
        Else
        .Close False
        End If
    
        Select Case response
        
        Case vbYes
        bIsClosing = True
        bMadeChanges = False
        HideAll
        .Close False
        bIsClosing = False
            
        Case vbCancel
        Cancel = True
        
        Case vbNo
        .Saved = True
    
        End Select

    End With

End Sub

Private Sub Workbook_Open()
Call ShowAll
ThisWorkbook.Sheets("Macros Disabled").Activate
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If SaveAsUI Then
    MsgBox "Sorry, you are not allowed to use Save As.", vbCritical, "No Save As"
    Cancel = True
    Exit Sub
    End If

If bIsClosing Then Exit Sub
Call HideSaveShow
Cancel = True
bMadeChanges = False

End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
bMadeChanges = True
End Sub

Thanks for reading.
If this was inventing the wheel again, nevermind, it was a pleasure
Anyway I hope this topic will help you to enhance your project!

kind regards,
Erik

 

[m0ckin9bird]

Hi! i´m using the solution as it is written but i also added a macro in another module wich filter per user the sheets shown. My problem is that everything works fine (ask for enabling macros, my macro runs, not printable, not save as possible) BUT when I click on save icon all the sheets are shown again ruining the security between users...

this is the code of the macro to filter security per user:

Sub Autpen()

administradores = "admin1, admin2"


administrador = Split(LCase(administradores), ",")


usuario = LCase(Application.UserName)
For i = 0 To UBound(administrador)
posicion = posicion + InStr(usuario, administrador(i))

Next

If posicion = 0 Then

ElseIf usuario = "user1" Then
Sheets("sheet1").Visible = xlSheetVeryHidden
Sheets("sheet2").Visible = xlSheetVeryHidden
Sheets("sheet3").Visible = xlSheetVeryHidden

Else

Sheets("sheet1").Visible = xlSheetVisible
Sheets("sheet2").Visible = xlSheetVisible
Sheets("sheet3").Visible = xlSheetVisible

End If
'grabamos los cambios
ActiveWorkbook.Save
End Sub

Can you help me? How can I fix this in order i can save without showing everything?

thanks in advance!

 

[BKunkle]

Although tricks like this allow you to lock down the workbook until macros are enabled, they can all be defeated if the user emails the workbook from within Excel once it has been opened and macros enabled. Typically, the recipient's computer will have macros disabled by default, so now the workbook is wide open with the macros disabled.

I have not been able to find a solution to the email problem. There appears to be no event triggered by the sending of email and it is difficult to disable the send command in Excel 2007.

However, I did work out a very nice reminder to enable macros that you can automate for users. It's quite simple:

In the workbook open event, write the time and date in an otherwise unused cell. Here's the VBA code to do that:
' if the sheet is protected, add: ThisWorkbook.Worksheets(1).unprotect "password"
ThisWorkbook.Worksheets(1).Range("M1") = Now
Application.Calculate ' prevent "flicker" of warning message
' if the sheet was protected, add: ThisWorkbook.Worksheets(1).protect "password"

You might also set the foreground and background colors of cell M1 to be the same (so no one sees this time and date). I also set the cell format to display both the date and the time.

You will probably want to duplicate this code in other events (such as the worksheet change event) or set it up to run on a timer.

Then, in any other unused cell(s), place this formula:
=IF((NOW()-M1) > 0.01,"Enable Macros!!","")
Format this cell as bold, red on white (or however you like). You may want to do it in multiple unused cells and on multiple sheets (tabs) to more effectively warn your users.

Change M1 to whatever cell you like. The 0.01 means one hundredth part of a day or 14.4 minutes.

The way this work is as follows:
Every time you open the workbook (or one of the above optional events fires) the time is written into cell M1--if macros are enabled.
The formula detects that the time is recent and shows no warning.
Any time the macros are disabled, the time stops getting updated in M1.
The formula detects this after 14.4 minutes and displays the warning.

If you open the workbook without enabling macros, you get the warning right away unless the macros had run less than 14.4 minutes before this (which is pretty unlikely). Note that the formula is only computed when the workbook is calculated.

This method should work even if someone emails the workbook and the recipient does not enable macros since the formula runs even if macros don't.

If the workbook won't work without macros enabled, and your users know that, then all they may need is a reminder to enable macros and this will do that for them.

And if they need to email the workbook, all they need to do is attach it to the email instead of emailing from within Excel and that problem will go away as well.

 

[erik.van.geit]

Sorry to all. I just noticed all those replies which I never saw till now. If you are still following and you want an answer, please reply again.