Hi all,

I have read through some past posts on this subject however am a
little stumped. If anybody could point me towards some resources
(preferably without lots of technical wording) where i can learn more
it would be great.

I've built a large wb used in an industry where the sheet
(specifically the functions / code & formula) would be worth a lot to
other companies. My company has a reasonably high turnover of staff
and it is not unusual for staff to take valuable wb like these with
them to their new role. However they need a copy of the wb whilst in
the company.

I need to build some super security into the sheet. My original
thoughts were to 1. Allow the sheet only to be opened on provision of
a pin 2. allow the sheet to only open until a certain date (or number
of opens) before the pin is required again 3. during this period of
time allow the sheet only to work on a certain computer (based on the
computer name) so if the sheet was transferred to another computer the
pin would be required again

I have read some posts saying that building this functionality within
excel is fairly pointless as would be cracked reasonably easily,
however i am unsure where / on what prog (i have no experience of
coding outside of office applications) i would write this and how i
would call it with my wb.

All help and suggestions very much appreciated. Thanks, K

Store a back-up copy of your workbook without the following code. Then set a
future date for the workbook to selfdestruct with:

SelfDestructDate = 7/4/08
If Date = SelfDestructDate
For Each Sheet In ActiveWorkbook.Sheets
Cells.Clear
Next
End If

If someone steals a copy of the workbook, it will only work until the date
you set.
You will just have to remember to go in and reset the date periodically to
keep from losing current data.


"anon" wrote:

> Hi all,
>
> I have read through some past posts on this subject however am a
> little stumped. If anybody could point me towards some resources
> (preferably without lots of technical wording) where i can learn more
> it would be great.
>
> I've built a large wb used in an industry where the sheet
> (specifically the functions / code & formula) would be worth a lot to
> other companies. My company has a reasonably high turnover of staff
> and it is not unusual for staff to take valuable wb like these with
> them to their new role. However they need a copy of the wb whilst in
> the company.
>
> I need to build some super security into the sheet. My original
> thoughts were to 1. Allow the sheet only to be opened on provision of
> a pin 2. allow the sheet to only open until a certain date (or number
> of opens) before the pin is required again 3. during this period of
> time allow the sheet only to work on a certain computer (based on the
> computer name) so if the sheet was transferred to another computer the
> pin would be required again
>
> I have read some posts saying that building this functionality within
> excel is fairly pointless as would be cracked reasonably easily,
> however i am unsure where / on what prog (i have no experience of
> coding outside of office applications) i would write this and how i
> would call it with my wb.
>
> All help and suggestions very much appreciated. Thanks, K
>

I like the code however anybody could password crack and find this
code and delete it, or simply open the sheet without macros and still
see the formula & code. I need something more secure than this.

u must search.. (not sure but think on ron de bruins or chip pearson
name/site)

u can very hidden all sheets accept one. with a macro that tells the
workbook to open all very hidden sheets if macro is enabled. in that case it
will go and check the date for self destruct. if macro's disabled u will not
be able to see any of the sheets in any way.

i never used this before but by sounds of it look like something u could
use. i found it by searching the posts here. then u will find a link to a
web page.


something they state clear though is that no matter what u do it can be
cracked.....

i downloaded some example workbooks from the site with expire codes etc.

Yeah, VBA does not lend itself to a lot of security. Philip has touched on
another approach, set up several levels of security and give them a maze to
work through to get to where the critical code is. Make them at least work
for what they steal.<g>

"anon" wrote:

> I like the code however anybody could password crack and find this
> code and delete it, or simply open the sheet without macros and still
> see the formula & code. I need something more secure than this.
>

OK I understand I cannot make a wb entirely crack proof however I'm
trying to cover all bases to get it as close as possible. I'd
appreciate if anyone could spot any weakspots in the below process
i've written or suggest any other ways to make it more difficult for
the nasty people trying to get in!;

1. If wb opened with macro's disabled only a splash screen is visible,
all other sheets are very hidden (obviously wb and vb are protected
with very long alphanumeric passwords)

2. On first use wb creates a code (based on computer name and date)
and asks for an activation code (given to them by me). 3 attempts at
inputting the wb code and the wb deletes all sheets (no warning for
sheet deletion)

3. Once activated the wb is valid for 90 days or 30 uses, when it will
then ask for another code. 3 attempts and workbook deletes all sheets
(this won't be a problem as wb is not used to store data). No warning
for sheet deletion (to prevent users looking for the code)

4. If wb used on another computer it will recognise it is not the
computer activated on and ask for another activation code as per step
2.

5. wb code well hidden among rubbish and the sheet storing the data is
set to very hidden, plus the actual data on the sheet is hidden among
lots of ubbish! If this data deleted the main code modules will not
run as they check for this data being in place and if not there sheets
are deleted

This way basically wb cannot be distributed to other users, and
certain users could not use the wb after a certain length of time or
number of uses without an activation code. If a user discovered the
security code and deleted it the main code module would check for this
and delete all sheets.

How does that sound?!!

This from Chip Pearson's site says it all.

Unfortunately, no VBA-based time-bombing method is foolproof. A skilled user can
quite easily circumvent the time-bomb code and get full access to the workbook,
even after it has expired. The code on this page also requires that the user has
macros enabled. If macros are disabled, the code will not work. (See Ensuring
Macros Are Enabled for code to force the user to enable macros.) All that said,
the time-bomb methods presented here are probably "good enough". They will not
prevent an experienced (and dishonest) user from circumventing the protection,
but the code will work for the vast majority of Excel users. All protection
mechanisms in Excel are really intended to prevent the user from accidentally
changing a value or some code. The security just isn't strong enough to provide
real protection of proprietary information or information or code with
intellectual property value. For that level of security, you should be writing
your code in Visual Basic 6 or VB.NET. See Creating A COM Add-In for details
about creating a COM Add-In and Automation Add Ins A Function Libraries for
details about creating an Automation Add-Ins.

http://www.cpearson.com/excel/workbooktimebomb.aspx

Go to the site and click on the links for Creating a COM add-in and the link to
Automation Add-ins.


Gord Dibben MS Excel MVP

On Sat, 22 Mar 2008 12:01:49 -0700 (PDT), anon <(E-Mail Removed)>
wrote:

>OK I understand I cannot make a wb entirely crack proof however I'm
>trying to cover all bases to get it as close as possible. I'd
>appreciate if anyone could spot any weakspots in the below process
>i've written or suggest any other ways to make it more difficult for
>the nasty people trying to get in!;
>
>1. If wb opened with macro's disabled only a splash screen is visible,
>all other sheets are very hidden (obviously wb and vb are protected
>with very long alphanumeric passwords)
>
>2. On first use wb creates a code (based on computer name and date)
>and asks for an activation code (given to them by me). 3 attempts at
>inputting the wb code and the wb deletes all sheets (no warning for
>sheet deletion)
>
>3. Once activated the wb is valid for 90 days or 30 uses, when it will
>then ask for another code. 3 attempts and workbook deletes all sheets
>(this won't be a problem as wb is not used to store data). No warning
>for sheet deletion (to prevent users looking for the code)
>
>4. If wb used on another computer it will recognise it is not the
>computer activated on and ask for another activation code as per step
>2.
>
>5. wb code well hidden among rubbish and the sheet storing the data is
>set to very hidden, plus the actual data on the sheet is hidden among
>lots of ubbish! If this data deleted the main code modules will not
>run as they check for this data being in place and if not there sheets
>are deleted
>
>This way basically wb cannot be distributed to other users, and
>certain users could not use the wb after a certain length of time or
>number of uses without an activation code. If a user discovered the
>security code and deleted it the main code module would check for this
>and delete all sheets.
>
>How does that sound?!!

pswanie at gmail com

is there any way that u can email me a copy of your wb? i dont need to see
any of your data. and im from SA so all your formulas would not be
jeopordised... or if you can send me a wb with couple blank sheets. i wana
have a look at the way u generate that code for the wb. im changing company
soon and thingking about implementing couple of things so they cant sell and
make money of my codes..

pswane at gmail dot com

"anon" wrote:

> OK I understand I cannot make a wb entirely crack proof however I'm
> trying to cover all bases to get it as close as possible. I'd
> appreciate if anyone could spot any weakspots in the below process
> i've written or suggest any other ways to make it more difficult for
> the nasty people trying to get in!;
>
> 1. If wb opened with macro's disabled only a splash screen is visible,
> all other sheets are very hidden (obviously wb and vb are protected
> with very long alphanumeric passwords)
>
> 2. On first use wb creates a code (based on computer name and date)
> and asks for an activation code (given to them by me). 3 attempts at
> inputting the wb code and the wb deletes all sheets (no warning for
> sheet deletion)
>
> 3. Once activated the wb is valid for 90 days or 30 uses, when it will
> then ask for another code. 3 attempts and workbook deletes all sheets
> (this won't be a problem as wb is not used to store data). No warning
> for sheet deletion (to prevent users looking for the code)
>
> 4. If wb used on another computer it will recognise it is not the
> computer activated on and ask for another activation code as per step
> 2.
>
> 5. wb code well hidden among rubbish and the sheet storing the data is
> set to very hidden, plus the actual data on the sheet is hidden among
> lots of ubbish! If this data deleted the main code modules will not
> run as they check for this data being in place and if not there sheets
> are deleted
>
> This way basically wb cannot be distributed to other users, and
> certain users could not use the wb after a certain length of time or
> number of uses without an activation code. If a user discovered the
> security code and deleted it the main code module would check for this
> and delete all sheets.
>
> How does that sound?!!
>
>