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

 

[erik.van.geit]

Hi,

I got a little comment in another thread.
Problem
When you start a new workbook and have put your code in the workbookmodule, you won't be able to save the first time (because SaveAs is disabled).
Solution (among others)
add a line "Exit Sub"
save
delete the line again

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 
Exit Sub
'other code

best regards,
Erik

 

[erik.van.geit]

update

When you saved the workbook, the first sheet was always selected, which is annoying.
To fix this: Insert a few lines within the code.
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

I updated the code in initial post.

best regards,
Erik

 

[wsl]

Just wonder why Save As needs to be disabled? It's a little annoying since Save As is very commonly used. Is there a way to get around this?

Thanks!

 

[tusharm]

I realize this was originally posted last year but...

It's a lot easier and a heck of a lot more consumer friendly to deploy code as an add-in. Not to mention that an add-in is the only practical way to update code once it's distributed.

 

[wsl]

In my case, version control will be there, so macros and add-in I guess is the same case here.

Any specific suggestions to get around? Thanks!

 

[sudburymetoo]

I have used Erik's code and my file is on a network drive. The show all fails sometimes even when macros are enabled. It works after trying for a couple of times. any ideas ?

 

[sudburymetoo]

oops sorry,I was not supposed to post here, missed Erik's request at the top

 

[nishpearl123]

Thank a lot... the solution is working well.

 

[Jaafar Tribak]

Hi erik.

Just wanted to document this new approach here. (Read my post #6 )

I've tested this new technique only on Excel 2003 Win XP so I am not sure it works accross other platforms. Any feedback or suggestions for improvement are most welcome.