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?

Create a Workbook_BeforeSave and a Workbook_Close macro that protects
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 on
ENABLE MACROS will present the user with a sheet that can be edited
since the Workbook_Open event won't run and unprotect it without macros
being enabled.

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


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=46287

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

Code:
--------------------
ActiveWorkbook.Protect Password:="PASSWORD", Structu=True, Windows:=False
ActiveSheet.Protect Password:="PASSWORD", Contents:=True, Scenarios:=True
--------------------
Substitute PASSWORD for whatever password you wish, just amend the code
for UnProtect.JBeaucaire;167068 Wrote:
Create a Workbook_BeforeSave and a Workbook_Close macro that protects
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 on
ENABLE MACROS will present the user with a sheet that can be edited
since the Workbook_Open event won't run and unprotect it without macros
being enabled.

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


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=46287

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.

--
Regards,

OssieMac


"Simon Lloyd" wrote:


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

Code:
--------------------
ActiveWorkbook.Protect Password:="PASSWORD", Structu=True, Windows:=False
ActiveSheet.Protect Password:="PASSWORD", Contents:=True, Scenarios:=True
--------------------
Substitute PASSWORD for whatever password you wish, just amend the code
for UnProtect.JBeaucaire;167068 Wrote:
Create a Workbook_BeforeSave and a Workbook_Close macro that protects
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 on
ENABLE MACROS will present the user with a sheet that can be edited
since the Workbook_Open event won't run and unprotect it without macros
being enabled.

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


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=46287

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", Structu=True, Windows:=False
ActiveSheet.Protect Password:="PASSWORD", Contents:=True, Scenarios:=True
End Sub
--------------------


And another to Unprotect:

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


Then use the Workbook_BeforeSave to do the work for you:

Code:
--------------------
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ProtectBook
ThisWorkbook.Save
UnprotectBook
End Sub
--------------------


To just close, a Workbook_BeforeClose fixes the protection in place:

Code:
--------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ProtectBook
End Sub

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

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

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


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=46287

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.

--
Regards,

OssieMac


"JBeaucaire" wrote:


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", Structu=True, Windows:=False
ActiveSheet.Protect Password:="PASSWORD", Contents:=True, Scenarios:=True
End Sub
--------------------


And another to Unprotect:

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


Then use the Workbook_BeforeSave to do the work for you:

Code:
--------------------
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ProtectBook
ThisWorkbook.Save
UnprotectBook
End Sub
--------------------


To just close, a Workbook_BeforeClose fixes the protection in place:

Code:
--------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ProtectBook
End Sub

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

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

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


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=46287

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.

Here is a link to a site where you can download a sample workbook.
http://www.dotxls.com/excel-security...e-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

--
jb


"Samuel Looney" wrote:

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?

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.

Samuel Looney wrote:

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?

--

Dave Peterson

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





"Samuel Looney" wrote:

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?

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





"Samuel Looney" wrote:

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?