Ensuring Macros are running

[ccarmock]

I have an Excel 2003 Application taht uses Excel Macros to protect cells
within the sheet as data is entered. This is working well so far.

Before saving it I have a Macro I run to hide all the useful sheets and
then Auto_Run is set to unhide them again. Thus if Macros are
disabled whent he sheet is opened, Auto_Run won't run and the sheets
won't be accessible.

The problem I have is that I, currently, have no way to prevent a user
saving the sheet with all tabs visible and then reopening with Macros
disabled, and then the Macros that run when data is entered do not run
either.

One way round this would be to force a Macro to run when the sheet is
saved, which could rehide the appropriate sheets again. Alternatively
if the normal System Save can be disabled I coudl then force the user
to save via a Macro attached to a button.

Does anyone have any ideas that could help here?

Regards
CLive

 

[dominicb]

Good evening Clive

Macros are pretty powerful things can irreversibly damage or remove
files and for this reason Microsoft have implemeted some protection for
the user from rogue macros. You have a few possible actions:
1. Set your macro security to low - not recommended for obvious
reasons, and anyway a user could easily reset it to medium to prevent
the macro from running.
2. Purchase yourself a digital certificate and set yourself up as a
trusted source so that macros signed by you will run unchecked.
3. Design the macro as a seperate file run as an add-in as this will
run without giving the user the chance to cancel it.
4. Save the file with all the sheets (except perhaps a warning sheet
telling the user that macros must be run) hidden (or xlVeryHidden) and
use a macro to unhide all the sheets and hide the warning sheet.

Be aware that none of these methods is foolproof and if someone really
wanted to break your system and access information that perhaps they
shouldn't then you will struggle to stop them with Excel.

HTH

DominicB

 

[Guest]

Put your 'hiding' routine in the 'ThisWorkbook' 'BeforeClose' section.
HTH,