Password security problem: view of the hiden worksheet in xlsm and xlsx file !?

[Ivan]

Hello,

if you have an excel file of the type xlsm and if you protect the VBA code
with a password then you disable the user from seeing not only the code but
also from seeing the structure of worksheets, among them especially the
'very hiden' worksheets too.

An over inquisitive user can but save the VBA password protected xlsm file
as an xlsx file. In such a case the user besides he lose the VBA code he
get the access to all of the worksheets where he can then change the
properties and finally he get the access to the data of the before hiden
worksheets.

Is there any way to avoid to make user possible to view the data he shoud
not see?

Ivan

 

[Jim Thomlinson]

There is absolutely no way to effectively protect data within an XL
spreadsheet. Any passwords can be broken (some are more difficult than others
but all can be broken). Very hidden sheets can easily be unhidden with very
simple code. In short if you are worried about security XL is not a good
platform to choose.

Run this code against any workbook to view all sheets

dim wks as worksheet
for each wks in activeworkbook.Worksheets
wks.visible = xlsheetvisible
next wks

(it misses xl4 macro sheets but that is easy to view also if you want)

If the workbook is protected then there is simple code that you can download
to hack the password.

 

[Ivan]

Searched by Google I saw (but I don't see the same in my Outlook Express ?)
that Jim Thomlinson have replied to my question and he has stressed that
Excel is completly unsafe and that it is always possible to break into the
code and into the structure of worksheets.

I agree with his thinking. However within bounds of practicability I wanted
to secure my workbook on a such way to make it impossible the entry into the
code and the view into the structure of worksheets for all ordinary users.

I think I found one way how to prevent user from seeing the data of the very
hiden worksheets in the case when the user save the excel xlsm file as the
xlsx one. I can use the event BeforClose of the worbook in such case. In the
code of BeforClose event it must be asked if the ActiveWorkbook.FileFormat
is equal 52 ( i.e. format is xlsm) and if this number is not 52 all sheets
with confidential data must be cleared:

If ActiveWorkbook.FileFormat <> 52 then
Sheets("NameOfSheet").Select
Cells.Select
Selection.ClearContests
ActiveWorkbook.Save
End If

Ivan