Determining if the user enabled macros

[Samuel Looney]

How can it be determined whether or not a user enabled macros? The workbook I
created has a lot of code that validates data input and I do not want anyone
to change the information unless the macros are enabled. Any ideas?

 

[JBeaucaire]

Create a Workbook_BeforeSave and a Workbook_Close macro that protect
the sheet completely from any changes

Then put in a Workbook_Open macro to unprotect the sheet automatically

Since the protection is occuring in the background, only clicking o
ENABLE MACROS will present the user with a sheet that can be edite
since the Workbook_Open event won't run and unprotect it without macro
being enabled

Also, once you've done this, be sure to password protect the code i
the VBE, too, so they can't read the code without a password, perhap
the same one you're protecting and unprotecting with in the background

 

[Simon Lloyd]

You will need to protect both the worksheet(s) and workbook structur
like this

Code
-------------------
ActiveWorkbook.Protect Password:="PASSWORD", Structure:=True, Windows:=Fals
ActiveSheet.Protect Password:="PASSWORD", Contents:=True, Scenarios:=Tru
-------------------
Substitute PASSWORD for whatever password you wish, just amend the cod

Create a Workbook_BeforeSave and a Workbook_Close macro that protect
the sheet completely from any changes

Then put in a Workbook_Open macro to unprotect the sheet automatically

Since the protection is occuring in the background, only clicking o
ENABLE MACROS will present the user with a sheet that can be edite
since the Workbook_Open event won't run and unprotect it without macro
being enabled

Also, once you've done this, be sure to password protect the code i
the VBE, too, so they can't read the code without a password, perhap
the same one you're protecting and unprotecting with in the background

--
Simon Lloy

Regards
Simon Lloy
'The Code Cage' (http://www.thecodecage.com

 

[OssieMac]

It will depend on the users whether the previous answers will work
satisfactorily.

If protection is inserted in a Workbook_BeforeSave event then if the user
decides to periodically save their work (which is good practice) then
immediately they save they are locked out of doing any more work until they
close and re-open.

If protection code is inserted in just the Workbook_BeforeClose (without the
Workbook_BeforeSave) then the workbook must be saved again before the
protection takes effect so if you have a smart user then all they have to do
is save the workbook then close it and at the prompt to save they just answer
No and it will close without the protection.

If you include Save code in the Workbook_BeforeClose event to overcome the
above then you are treading dangerous ground. If a user messes up (and it
happens) and they want to close the workbook without saving then they can't
do it.

I am not saying don't use the the suggestions that have been provided; just
be aware of their linitations.

 

[JBeaucaire]

I would suggest a set of simple macros, all hidden

One macro secretly password protects the whole thing

Code
-------------------
Private Sub ProtectBook(
ActiveWorkbook.Protect Password:="PASSWORD", Structure:=True, Windows:=Fals
ActiveSheet.Protect Password:="PASSWORD", Contents:=True, Scenarios:=Tru
End Su
-------------------

And another to Unprotect

Code
-------------------
Private Sub UnprotectBook(
ActiveWorkbook.UnProtect Password:="PASSWORD
ActiveSheet.UnProtect Password:="PASSWORD
End Su
-------------------

Then use the Workbook_BeforeSave to do the work for you

Code
-------------------
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean
ProtectBoo
ThisWorkbook.Sav
UnprotectBoo
End Su
-------------------

To just close, a Workbook_BeforeClose fixes the protection in place

Code
-------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean
ProtectBoo
End Su

-------------------

Structured properly, your saved book is protected, but he user can kee
using it because the protecting and unprotecting keeps occuring in th
background

If they crash their program, they lose what they had anyway, the save
version WILL still be usable and macros still working

 

[Héctor Miguel]

hi, Samuel !

How can it be determined whether or not a user enabled macros?
The workbook I created has a lot of code that validates data input and
I do not want anyone to change the information unless the macros are enabled. Any ideas?

one (wild) idea (you need to protect your vba-project):
since you need to restrict the use *only* to macros enabled...

- use the '_beforeclose' event to:
- set the workbook property "IsAddin" to true
- save the workbook
- close the workbook

- use the '_open" event to set its property "IsAddin" to false

pros: your workbook will be *operational* ONLY if macros are enabled
cons: *IF* the user does not enable the macros... (probably) will have to restart excel -?-

hth,
hector.

 

[OssieMac]

To JBeaucaire.,

When the Workbook_BeforeClose event runs it does run the protect code but
that is a change to the workbook and that causes Excel to open a diaglog box
and ask the user if they want to save. If the user answers No then the
workbook closes as it was last saved before the Workbook close event ran and
hence no protection.

If you suppress the dialogbox asking the user if they want to save by using
Application.DisplayAlerts = False then this is dangerous ground because it
prevents the user from exiting the workbook without saving if they mess up
and want to close without saving and re-open and start again.

 

[john]

Here is a link to a site where you can download a sample workbook.
http://www.dotxls.com/excel-security/28/how-to-make-a-user-enable-excel-macros

this may or may not do what you want to achieve. However, be mindful of what
OssieMac is saying with regard to giving user the option to discard any
changes they have made.
Forcing a workbook to be saved will most likely lead to unwanted data
corruption problems.

Hope helpfull

 

[Dave Peterson]

Any suggestion that depends on code to check to see if macros are enabled would
involve macros--and if macros are disabled, they wouldn't work.

One way that you could make the workbook unusable is to create a User Defined
Function. The UDF would break if macros are disabled.

So you could use:

Option Explicit
Function myFunc()
myFunc = 0
End Function

Then you could change important formulas (that return numbers) from something
like this:

=a1+b1
to
=a1+b1+myfunc()

When excel recalculates (usually when it opens, too), the formula will either
evaluate ok (with macros enabled) or return a #NAME? error.

============
If you want to force the user to open your workbook with macros enabled...

(Saved from a previous post)

You could create another workbook that opens your real workbook and then closes
itself. The put a shortcut to that helper workbook on your desktop.

That real workbook could have the password built into it.

Option Explicit
Sub auto_open()

Dim myPWD As String
Dim wkbk As Workbook

myPWD = "hi"

Set wkbk = Workbooks.Open(Filename:="C:\my documents\excel\book1.xls", _
Password:=myPWD)

wkbk.RunAutoMacros which:=xlAutoOpen

'ThisWorkbook.Close savechanges:=False

End Sub

When you're done testing, uncomment that last line. It closes the helper
workbook without saving--could be a pain while you're testing.

 

[JBeaucaire]

When the Workbook_BeforeClose event runs it does run the protect cod
but that is a change to the workbook and that causes Excel to open
diaglog box and ask the user if they want to save. If the user answer
No then the workbook closes as it was last saved before the Workboo
close event ran and hence no protection

If you look at the logic in the macros I suggested, every time the boo
saves it is protected FIRST. So, if they try to CLOSE and say NO t
saving, then the workbook available is the last saved one which IS
protected version

I've used this approach with hidden sheets effectively, so th
protection scheme seems just as sound

 

[Hannah Lu]

Hi Samuel,
I usually will create a "Welcome Message" sheet that says something like
"Please enable macros to continue," usually with brief instructions in case
they don't know how. That way when they open the workbook if they see the
Welcome Message sheet they don't have macros enabled and need to enable them
to make the other sheets visible.
Then the code looks like this:

Private Sub Workbook_Open()
'Hides the Welcome Message Sheet and displays other sheets
Worksheets("Sheet1").Visible = True
Worksheets("Sheet2").Visible = True
Worksheets("Welcome Message").Visible = xlVeryHidden
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Worksheets("Welcome Message").Visible = True
Worksheets("Sheet1").Visible = xlVeryHidden
Worksheets("Sheet2").Visible = xlVeryHidden
End Sub

You want to be sure that you order the show/hide procedures as shown above,
you'll get an error if, in the BeforeClose event, you hide Sheet1 and Sheet2
before showing Welcome Message.
Good luck!
-Hannah

 

[Hannah Lu]

Hi Samuel,
I usually will create a "Welcome Message" sheet that says something like
"Please enable macros to continue," usually with brief instructions in case
they don't know how. That way when they open the workbook if they see the
Welcome Message sheet they don't have macros enabled and need to enable them
to make the other sheets visible.
Then the code looks like this:

Private Sub Workbook_Open()
'Hides the Welcome Message Sheet and displays other sheets
Worksheets("Sheet1").Visible = True
Worksheets("Sheet2").Visible = True
Worksheets("Welcome Message").Visible = xlVeryHidden
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Worksheets("Welcome Message").Visible = True
Worksheets("Sheet1").Visible = xlVeryHidden
Worksheets("Sheet2").Visible = xlVeryHidden
End Sub

You want to be sure that you order the show/hide procedures as shown above,
you'll get an error if, in the BeforeClose event, you hide Sheet1 and Sheet2
before showing Welcome Message.
Good luck!
-Hannah