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?

 

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

 

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

 

[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