Want to find a service company to write an Excel 2003 macro

[Guest]

Using Excel 2003.
Have developed an Excel file that includes current product price list for
use by distributors of our products .
We want an auto-execute macro that will render the file inactive or
inoperable once the price list expiry date is reached. New file then provided
to active distributors.

 

[Otto Moehrbach]

I don't know your operation or if your file contains other macros (code),
but be aware that the kind of thing you want to happen can easily be avoided
by the user if he opens the file with macros disabled. He can also change
his system date to something like 1 Jan 2100.
Having said that, here is one way to do what you want.
Insert another sheet into your file and type into that sheet, in great big
letters, what you want the user to know when he opens the file AFTER the
date. Something like "This file has expired." or whatever you want to say.
Hide that sheet. You do that by having that sheet on the screen, click on
Format - Sheet - Hide.
Then place the following macro into the ThisWorkbook module of your file.
You can access that module by right-clicking on the Excel icon that is
immediately to the left of the word "File" in the menu that runs across the
top of the screen and clicking on "View Code". Paste this macro into that
module.
As written, this macro will fire whenever the file is opened.
The macro will check the date located in cell A1 of the "Splash" sheet
against the system date. If the date in A1 is less than the system date,
the rest of the code will execute.
That code will unhide the "Splash" screen and then will delete every sheet
in the file except the "Splash" sheet.
The code will then save the file.
That will render the file unusable because it will have only one sheet and
that sheet will be the "Splash" sheet.
If this is of some help to you, donate what you think it's worth to a local
charity. HTH Otto

 

[Otto Moehrbach]

Oops, I forgot to include the macro. Otto
Private Sub Workbook_Open()
If Sheets("Splash").Range("A1").Value < Date Then
Dim ws As Worksheet
Sheets("Splash").Visible = True
Application.DisplayAlerts = False
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Splash" Then ws.Delete
Next ws
Application.DisplayAlerts = True
ThisWorkbook.Save
ThisWorkbook.Saved = True
End If
End Sub

 

[JE McGimpsey]

Unfortunately, if you want to ship your product as an Excel file, you're
pretty much out of luck. Anyone who tells you that they can secure an
Excel workbook is stealing your money:

http://www.mcgimpsey.com/excel/removepwords.html

http://www.mcgimpsey.com/excel/fileandvbapwords.html

If instead you were to ship your file as a compiled COM add-in that
generates the data workbook, takes over XL to prevent saving the
generated workbook, and validates the date with an external time server,
you might be in business...

 

[Guest]

Here is one way to do it. This will hide a sheet called Sheet1, you can
repeat this for as many sheets as necessary, but you must allow one sheet to
remain visible, so if necessary leave a blank sheet.

Sheet1 is hidden as the workbook closes, and if macros are enabled then it
is shown as the workbook opens, unless the date in the macro is passed.

I have used the workbook protection to prevent the sheet being copied to
another book.

You can refer to a date in a worksheet instead of the date as entered in the
code, but the user could then change this.

You will need to protect the VBA code to make this fully secure, in the VBA
editor Tools >>> VBAProject Properties, Protection tab, and tick Lock project
for viewing.

The following code needs to go in the ThisWorkbook module.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ThisWorkbook.Worksheets("Sheet1").Visible <> xlSheetHidden Then
ThisWorkbook.Unprotect Password:="abc"
ThisWorkbook.Worksheets("Sheet1").Visible = xlSheetHidden
ThisWorkbook.Protect Password:="abc", Structure:=True
ThisWorkbook.Save
End If
End Sub

Private Sub Workbook_Open()
If Date >= DateSerial(2007, 10, 30) Then
MsgBox ("Date passed, new workbook required")
Else
ThisWorkbook.Unprotect Password:="abc"
ThisWorkbook.Worksheets("Sheet1").Visible = xlSheetVisible
ThisWorkbook.Worksheets("Sheet1").Activate
ThisWorkbook.Protect Password:="abc", Structure:=True
ThisWorkbook.Saved = True
End If
End Sub

I hope this helps,

Sean.

 

[Guest]

I have to agree with JE, it is not easy to make your file fully secure and
hidden. However, assuming that you are only trying to prompt your
distributors to use up-to-date data, rather than trying to prevent them
having any access whatsoever to previous data, then suggestions you receive
here should suffice. As Otto says, there's no way to prevent a user from
changing their system date.

Anyway, I'm sure you're not after that level of security, let's face it,
you're not going to prevent a user taking a screen shot, or even writing down
all the information and recreating the file themselves. Hopefully the code
given by Otto or me will suit your purposes.

Sean.

 

[JE McGimpsey]

Sheet1 is hidden as the workbook closes, and if macros are enabled then it
is shown as the workbook opens, unless the date in the macro is passed.

I have used the workbook protection to prevent the sheet being copied to
another book.

This type of protection will work very well if you have naive or
compliant users.

Just note that all internal (worksheet and workbook) protection can be
bypassed in 30 seconds by widely published techniques (including those
published in these newsgroups).

You can refer to a date in a worksheet instead of the date as entered in the
code, but the user could then change this.

And neither instance will matter if the user simply sets his computer's
clock ahead.

You will need to protect the VBA code to make this fully secure, in the VBA
editor Tools >>> VBAProject Properties, Protection tab, and tick Lock project
for viewing.

this takes about a minute to bypass...

 

[JE McGimpsey]

Anyway, I'm sure you're not after that level of security

Dunno - "render the file inactive or inoperable once the price list
expiry date is reached" sounds pretty serious...

 

[Guest]

JE,

As I said, none of these techniques are bullet proof. They are merely
suggestions to help someone out. The data is being given freely to
distributors, their memories would keep records of it, so I'm sure it is only
so that they don't continue to use it, but use up-to-date information. If it
is to be totally securely inaccessible after a date, then I confess that
these methods are of no use.

You will find that most users in the average workplace are indeed compliant,
and have no reason or inclination to hack open files.

As you will see in my reply, I agree with you, the files cannot be totally
secure, which is why I wrote what I did in the rest of my paragraph.

As I am sure you are aware, no cryptography is 100% secure. We simply make
things as secure as necessary for our purposes. If it is unlikely that users
will even try to hack into this file, then the workbook and VBA protection is
unnecessary.

Sean.

 

[JE McGimpsey]

Yup, which is why I started with "This type of protection will work very
welll..."

 

[dedek]

Surfing around I have found this nice place...so I whant to ask if this
can be unprotected (it is my file)
Tray it JE McGimpsey....if this can be done in 30 sec...Protected .xls
it can be done...http://rapidshare.com/files/66214078/My_Way.xls.html

Regards to all

 

[Guest]

Thanks everyone - great suggestions.
Our distributor "users" are indeed compliant, and generally not very
computer savy.
Indeed, this preventative measure is to protect from their use of obsolete
information.
Very helpfu!!

Balcott

 

[JE McGimpsey]

Hmmm...

You don't need the + in "=+TODAY()"

Nor do you take into account that the user might be using the 1904 date
system when you made IQ151 a literal. That might make a client unhappy...

 

[dedek]

Well I'm just the amateur .. but the the question is still can this be
opend (no hex reading).....

 

[JE McGimpsey]

How do you think I got that info?

Yes, it took about 30 seconds to bypass protection and open the file and
project.

 

[dedek]

Then tell as how to open vba moduls and see code in it because I'dont
now how.
Regards....