Excel macro security
Posted on 2014-03-28
Experts, i have a sensitive cost analysis spread sheet that is given to sales people on our staff. We have sales people all over the country and they come and they go. And many times they take our sensitive data with them to a competitor. This is very damaging. If i were in charge of how this information was distributed i would be writing applications using encrypted access databases as back ends to an application rather than using spreadsheets. But I am not in charge of how this is done and have only been asked to help find a quick solution to the problem. I am not that familiar with office automation so i need some help.
What I have done is written a DLL that uses the MD5 hash. The serial number of the hard drive is used as a reg key. Then it will be emailed to an administrator who will have an admin program to keep track of all the users and their activation codes. The admin program will hash the reg key and that will be the user's activation code.
When the user enters the activation code the dll will hash the reg key (hard drive serial number) and then compare it to the the activation code given to the user. If they match then the spread sheet will run. If not then it will never be opened. This way the spread sheet is locked to the company computer and cannot be used outside of the company.
So all is great and everything is ready to go except for one thing. I dont know what to do about the "Enable Macro Security" warning that will be displayed when the spread sheet is first opened. None of my code can run until the user enables this. Kinda puts the breaks on my whole plan. Is there anything i can do to run my code prior to this or set the macro security before the workbook opens?