Excel macro security
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Another possible approach may be to use a digital signature to verify that your file is trustworthy. I have no experience with these at all, but you may find some useful information via the below links:
http://www.experts-exchang
http://office.microsoft.co
This was the most informative thread for me: http://www.mrexcel.com/for
Mods,
Feel free to delete the links if necessary, but I thought it worth a shot, since I was notified about this by email as a Neglected Question.
hth
Rob
http://www.experts-exchang
http://office.microsoft.co
This was the most informative thread for me: http://www.mrexcel.com/for
Mods,
Feel free to delete the links if necessary, but I thought it worth a shot, since I was notified about this by email as a Neglected Question.
hth
Rob
Nice one, I'll leave you in the hands of the other Rob - it looks like he has a lot more personal experience to offer :-)
Good luck!
Good luck!
1. Create a license function that compares the user key to the PC's key and returns true if they match.
2. At the top of every function, call this function and exit the routine if license function returns a false value.
3. Make sure you have enough functionality in your code that any worksheet and the entire workbook is worthless without a valid user key on the hard drive.
2. At the top of every function, call this function and exit the routine if license function returns a false value.
3. Make sure you have enough functionality in your code that any worksheet and the entire workbook is worthless without a valid user key on the hard drive.
I wrote an article explaining how to allow macros before the file is opened https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/A_10805-Overcome-the-Trust-Center-Nuisance.html
ASKER
I have decided that broro183 had the best solution for me. What i have done is hide all the worksheets but 1. This sheet explains that the macro security must be enabled for the workbook to function. When the system is run for the first time and the user enables macros, the activation form is shown and the user can activate the software. When the software has been activated the system will search for the activation key and if it is found it will show the hidden worksheets and hide the first welcome worksheet that was displayed when the system was run for the first time.
During the first run if the user attempts to unhide the worksheets without activation the code in the On_activation of the workhseet will check for an activation code. If no activation code is found or is is found to be invalid then i will just call me.visible = false hiding the worksheet again.
When the workbook closes I will again hide all of the worksheets except for the welome worksheet.
During the first run if the user attempts to unhide the worksheets without activation the code in the On_activation of the workhseet will check for an activation code. If no activation code is found or is is found to be invalid then i will just call me.visible = false hiding the worksheet again.
When the workbook closes I will again hide all of the worksheets except for the welome worksheet.
HKCU\software\microsoft\of
And then set that to low, launch your Excel file and wait for it to finish, then reset the registry value to its original value afterwards.
Rob.