macros and file security

[N E Body]

Hi everyone

Just when I thought I had completed my latest project I find I have fallen
foul to the simplest of things!
I have created an Excel database <because our PCs dont have Access>. I
denied access to the speadsheet by using a userform on opening and blocking
all methods of getting past the form to the sheet behind. All entries are
via the userform. When the userform is closed the application closes!
Access to the sheet is via a textbox which requires a password.

All was working fine until I discovered if you alter Excels macro security
settings to prompt wether you want to disable macros anyone can access the
sheet simply by turning macros off when prompted during file opening.

Is there any way round this? If I set everyones macro security to low they
will still be able to get in by resetting the security level.
Is there any code I could put into the sheet to alter the setting or check
the setting and stop the file opening if the macros are to be disabled?

Thanks in advance

Kenny
Win 2000 Office 97 and 2000

 

[Edwin Tam]

I afraid it's always up to the user whether he/she wants the macros in a workbook to execute or not. Otherwise, someone can write a virus and force-execute it on anyone's computer.

If your workbook contains sensitive data, you can also hide your worksheets and the password-protect the structure of the workbook.

Regards,
Edwin Tam
http://www.vonixx.com

 

[papou]

Hello Kenny
There is no way around macro security settings.
If your wish is to prevent users from seeing your sheet in case macro
security is set to low then in VBE set the visible property of your sheet to
xlsheetveryhidden and password protect your project (remember to save
changes).
Access to your sheet will only be possible via VBE by unprotecting the
project with your password and setting the sheet visible property to
xlsheetvisible.

HTH
Cordially
Pascal

 

[Jamie Collins]

I have created an Excel database <because our PCs dont have Access>.

Neither does mine, I somehow managed to break the installation and
can't afford the time to rebuild my machine just now. And I'm
responsible for an app that is advertised as having an 'Access'
backend database!! Happily, I can get by just using ADO (actually,
I've found that I can do more with ADO than the MS Access UI allows).
Using the excuse of not having MS Access is just being defeatist <g>.

Jamie.

--

 

[Ed]

One method of making sure macros are turned on is:
* Create a new worksheet. Place a large block of text on this sheet
explaining that the user must have macros turned on to use the file.
* Set the data pages of the file to VeryHidden, leaving only the text block
visible to the user.
* Set an AutoOpen macro that sets the opening page to VeryHidden, sets the
data pages to Visible, and opens the UserForm.

HTH
Ed

 

[N E Body]

Thanks Ed

I wish I had received this post earlier - I have messed about all day and
arrived at the same result!

I dont know what this VeryHidden is that everyone keeps suggesting. I hide
the sheets on closing and password protect the workbook. Because you cannot
have all sheets hidden I left a blank sheet. An auto open macro unprotects
the workbook and makes the sheets visible. If the file is opened without
running macros and the workbook remains protected.

My blank sheet now has a "turn on macros" message - thanks Ed.

Regards
Kenny

 

[Harlan Grove]

One method of making sure macros are turned on is:
* Create a new worksheet. Place a large block of text on this sheet
explaining that the user must have macros turned on to use the file.
* Set the data pages of the file to VeryHidden, leaving only the text block
visible to the user.
* Set an AutoOpen macro that sets the opening page to VeryHidden, sets the
data pages to Visible, and opens the UserForm.

...

Depending on the level of user sophistication/perversity, users could open this
workbook with macros disabled, then create a new workbook with macros enabled,
and write macros in the second workbook to manipulate the first, unprotecting
and unhiding ABSOLUTELY EVERYTHING in the first workbook. No big deal to use an
internal password cracker first on the original workbook to make sure it's
completely unprotected.

Welcome to Excel development. There is no robust way to protect any content
stored in any Office application's document files.

Except . . . not entirely secure, but requiring more user sophistication to
crack, use a File Open password for your workbook. These are MUCH HARDER to
crack than internal passwords. Then use another workbook to open the
now-protected workbook via macros with macros enabled in it. The user could
still break into the second workbook and step through the macros, but as I said
above it'd require more sophistication.

Along this line, you could also write a small EXE using Automation (the facility
formerly known as OLE) to open or link to Excel and open the now-protected
workbook. It may be possible for users to step through such an EXE using a
debugger, but now it'd really take a high level of sophistication to beat the
system.

 

[Ed]

Welcome to Excel development. There is no robust way to protect any content
stored in any Office application's document files.

I guess it depends on whether you're trying to protect sensitive or
proprietary information from being available to unauthorized hackers, or
simply wanting to prevent fumble-fingered non-thinkers from screwing up your
data! I created a workbook one time: the visible front sheet was available
for use as a data input form, while the second sheet was hidden, collecting
the data and sorting it into a structure the database would easily import.
People kept screwing with the front sheet, which messed up my second sheet.
Had some frustrated phone calls when they couldn't get through the password
to make the first sheet "prettier" - but I kept my data input structure
safe!

Ed

 

[Ed]

Kenny -

These two Knowledge Base articles on VeryHidden might help you.
http://support.microsoft.com/default.aspx?scid=kb;en-us;213609&Product=xlw#appliesto
http://support.microsoft.com/default.aspx?scid=kb;en-us;213692&Product=xlw

Ed

 

[N E Body]

Ed

Yep it sounds as though Im following your footsteps here!
I am just stopping fumblers. All they get on opening is a userform for data
entry.
Dates can only be entered by a button so they cant screw up the date
formatting. Even signing off has to be via a code number so there is no
blaming anyone else. I have even blocked anyone changing recalled
information, The devil in me has set it to allow them to change the text
within the fields and save as usual - what they have not realized is
changed text does not get to the database!!!
Thanks for the links to VeryHidden

Kenny
www.handpicked.co.uk/crathornehall