PDA

View Full Version : In search of a few VBA solutions....



MRK_Toledo
09-03-2006, 09:23 AM
:help Here is what I have.....

I have made an excel workbook that has multiple sheets in it.

- I would like to keep the DEFAULT copy, and force the user to SAVE AS
- I would like to accomplish this through a message box
- I would like the SAVE AS to automatically enter a filename based on
info contained in a specific cell on a specific worksheet
- I would also like the SAVE AS to automatically point to the correct folder

In addition....

I would also like to be able to EXPIRE my workbook after a period of days.

- If the workbook expires in 90 days, then I would like for a message box
to open up each time the workbook opens reminding the user that
they only have 'x' days to renew their license.
- If that time passes ans they no not, I would like for them to be taken to
a seperate sheet in the workbook that only displays contact info and
info on how to get a fresh license, everything else will be locked out.

The fresh license will be a new updated file that they will be emailed.

This workbook does not contain any customer entered data, only reference information, so there will be no need to save their info upon expiring the app.

:dunno I have tried everything to get this to work, an I have had no luck. I have basically decided to start over from scratch and ask some of the experts that might be on the board.

Any help that you can provide will be greatly appreciated.

Thanks in Advance.

johnske
09-03-2006, 10:56 AM
...
I would also like to be able to EXPIRE my workbook after a period of days.

- If the workbook expires in 90 days, then I would like for a message box
to open up each time the workbook opens reminding the user that
they only have 'x' days to renew their license.
- If that time passes ans they no not, I would like for them to be taken to
a seperate sheet in the workbook that only displays contact info and
info on how to get a fresh license, everything else will be locked out.

The fresh license will be a new updated file that they will be emailed.

This workbook does not contain any customer entered data, only reference information, so there will be no need to save their info upon expiring the app...This needs to be addressed first.

There are several ways to go about this, but because Excel is an inherently insecure environment I would suggest that simply hiding worksheets would be too easy to overcome.

Probably a better way to go about this is to either kill the workbook at the end of the expiration period and replace it with a text document with the contact information. Or, next best, give a text document with the info and password protect the workbook.

geekgirlau
09-04-2006, 03:54 AM
And to address the first part of your post second :confused3 ...

Wouldn't this be addressed by saving your workbook as a template?

Ken Puls
09-04-2006, 10:03 AM
Hi there,

Totally agree with Anne (geekgirlau) on the template part. Save the file as an "xlt" (instead of "xls") file, and instruct the users to open it via "file|new" instead of "file|open".

You may also want to throw in some code at that point to ensure that it has been opened in xlt mode, rather than xls mode. I haven't ever done this myself, but I can't think it would be too hard. Probably just use the Workbook_Open event to check the file extension. If it's xlt then check if the username is yours. If it is, then open it as xlt, if not, give a message and close the file.

As far as the protection/expirey goes, you'll need to decide if you want the license to be based per user, per computer, or per workbook. Just as a suggestion...

For the first two, you can use a registry key to execute the following method:
-Upon opening check for the existence of a registry key
-->If it's not there, create it with today's date
-->If it is there, check the date
---->If it is over 90 days past, then give the user the message to buy a license
---->If it is equal to a certain date (say 01/01/1900) then it's licensed, so open without issue
---->Otherwise open it with the message about how many days are left

This would be set is a Local Machine key for "per computer" licensing, or a Local User key for "per user" licensing

If you want to work on "per workbook" licensing, then you'll need to come up with a way to write the date installed into the original file and check it all there.

Hope this helps with the ideas a bit. :)

MRK_Toledo
09-04-2006, 12:13 PM
Thanks for the info......that makes ssense, however.....I am not sure exactly how to accomplish this.

I am new to this whole thing. I have spent the last week buried in 3 different VBA books trying to figure all this out...still a little confused.

I am wondering how well my coding is up to this point.......would you be willing to take a look at my file?

Also, there would be a fair amount of trust placed due to the nature of this particular file.

Just wondering.

MRK_Toledo
09-04-2006, 12:32 PM
I found somebody to review my workbook......

thanks for all your help.

johnske
09-04-2006, 05:54 PM
We don't need to see the contents at this stage, just the VBA code. Remove or clear all sensitive worksheets and attach it, or just post the code (alter anything sensitive in code e.g. email addies)