Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Want to find a service company to write an Excel 2003 macro
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Want to find a service company to write an Excel 2003 macro
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 "Balcott" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Want to find a service company to write an Excel 2003 macro
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 "Otto Moehrbach" wrote in message ... 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 "Balcott" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Want to find a service company to write an Excel 2003 macro
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... In article , Balcott wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Want to find a service company to write an Excel 2003 macro
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", Structu=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", Structu=True ThisWorkbook.Saved = True End If End Sub I hope this helps, Sean. -- (please remember to click yes if replies you receive are helpful to you) "Balcott" wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Want to find a service company to write an Excel 2003 macro
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. -- (please remember to click yes if replies you receive are helpful to you) "SeanC UK" wrote: 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", Structu=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", Structu=True ThisWorkbook.Saved = True End If End Sub I hope this helps, Sean. -- (please remember to click yes if replies you receive are helpful to you) "Balcott" wrote: 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Want to find a service company to write an Excel 2003 macro
In article ,
SeanC UK wrote: 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... |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Want to find a service company to write an Excel 2003 macro
In article ,
SeanC UK wrote: 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... |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Want to find a service company to write an Excel 2003 macro
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. -- (please remember to click yes if replies you receive are helpful to you) "JE McGimpsey" wrote: In article , SeanC UK wrote: 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... |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Want to find a service company to write an Excel 2003 macro
Yup, which is why I started with "This type of protection will work very
welll..." In article , SeanC UK wrote: As I said, none of these techniques are bullet proof. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Want to find a service company to write an Excel 2003 macro
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 *** Sent via Developersdex http://www.developersdex.com *** |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Want to find a service company to write an Excel 2003 macro
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 "dedek" wrote: 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 *** Sent via Developersdex http://www.developersdex.com *** |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Want to find a service company to write an Excel 2003 macro
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... In article , dedek wrote: Tray it JE McGimpsey....if this can be done in 30 sec... |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Want to find a service company to write an Excel 2003 macro
Well I'm just the amateur .. but the the question is still can this be
opend (no hex reading)..... *** Sent via Developersdex http://www.developersdex.com *** |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Want to find a service company to write an Excel 2003 macro
How do you think I got that info?
Yes, it took about 30 seconds to bypass protection and open the file and project. In article , dedek wrote: Well I'm just the amateur .. but the the question is still can this be opend (no hex reading)..... |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Want to find a service company to write an Excel 2003 macro
Then tell as how to open vba moduls and see code in it because I'dont
now how. Regards.... *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I write a macro to show the find dialog box in excel 2003 | Excel Discussion (Misc queries) | |||
Excel 2003 in a WEB SERVICE - would like some input :) | Excel Programming | |||
Not able to write in published workbook using Excel Service SetRan | Excel Worksheet Functions | |||
Does anyone know of a service/company that builds macros? | Excel Discussion (Misc queries) | |||
Novice trying to call a web service using Excel 2003 | Excel Programming |