erik.van.geit
MrExcel MVP
- Joined
- Feb 1, 2003
- Messages
- 17,832
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
In "ThisWorkbook"module
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
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
Code:
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
Code:
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 SubThanks 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
