Enabling/Disabling Macros

[David]

I have a template which contains a macro. I would prefer
the users who use this template not be aware the macro is
there. i.e. I don't want any 'enable/disable macros Y/N'
popup windows appearing. I don't want the users to be able
to run the macro.
The users will fill in the spreadsheet data and email it
back to me. I will then run the macro to create a new
worksheet from the enetered data.
Is this possible? Or, is there a better way to do it?

 

[John Wilson]

David,

There are two parts to your question and different answers for each.

I don't want any 'enable/disable macros Y/N'
popup windows appearing.

If a macro exists, it's going to be flagged when the workbook opens.
If they have macros enabled, they won't get a warning.
If they have them disabled, they also won't get a warning.
If they have their security set to medium, they will get the
warning.....but........you said

I don't want the users to be able to run the macro.

At the top of the module where the macro resides, place the following:
Option Private Module
With the above option, any macros in that module won't appear on the
macro run list. Of course, if they're fluent in VBA, they could edit
that module and delete that line. You could protect the VBA
project but that password can be cracked too (just a warning)
Anyway, if you don't think they're capable of getting at the code
the Option Private Module should keep their grubby fingers
from running your macro.

John

 

[keepitcool]

David,

if users shouldnt be running the macro anyway you should take it out of
the template....

when it comes back the code to process his data can be anywhere on your
machine, no need to include it in the xlt.

then the warning wont popup either.

faik there's no way (and rightly so) to circumvent that popup if users
have decided not to set macro security on low. If someone would publish a
circumvention for that popup (if it could be done) he'd cause a REAL panic
at MS ... and with me too


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[David]

Thanks for the information.
Where are these security settings about macros to be found?
If I send a user my template and he opens it and
specifies 'disable macros', enters his data, saves it as
an XLS and sends it back to me, will I still be able to
run the macro or will it be disabled for me also?

By the way this macro does nothing dangerous, all it does
is perform calculations on the data entered and puts the
results in a new worksheet. Its just that its irrelevant
to the user so I'd rather they not be confused by seeing
any reference to macros.
- David

 

[John Wilson]

David,

Where are these security settings about macros to be found?

There's a good explanation in Excel (Tools/Macro/Security)

The security settings are PC (User) specific. If your workbook doesn't
depend on that macro to accomplish it's task, whatever settings
they open it with won't matter at all.

will I still be able to run the macro or will it be disabled for me

also?
If they e-mail the workbook to you and you have macros enabled,
you'll be able to run it (after you alter the code and comment out
that "Option Private Module" line of code)

I'd rather they not be confused by seeing
any reference to macros.

If they have security set to Medium, they'll see the warning.
If, as you say, it's not needed for the workbook and only
you will be running it on your PC, Ray's suggestion of placing
it in your Personal.xls workbook would seem like the best way
to go.

John

 

[John Wilson]

David,

all it does >is perform calculations on the data entered and
puts the results in a new worksheet.

Another option..........
Don't use any macros in that workbook.
Creat a new workbook just for yourself with a macro to open
that other workbook, run your macro on it, create your new
worksheet with calcs, etc. and save it.

Again, the Personal.xls is also an option.

John

 

[Tom Ogilvy]

Excel can't tell whether a macro is harmless or not. The security setting,
if not low, warns that there is a macro. It is then the users decision to
enable or disable macros - but the prompt will not be surpressed - there is
not setting for that.

Keep the macro - get the warning

Remove the macro (properly) - no warning.