Solved

Excel macro security

Posted on 2014-03-28
7
504 Views
Last Modified: 2014-03-29
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?
0
Comment
Question by:Basicfarmer
7 Comments
 
LVL 10

Accepted Solution

by:
broro183 earned 500 total points
ID: 39963563
hi,

Rather than setting the macro security before the workbook opens you can make the workbook useless if it is opened without macros enabled.
The common approach that I have seen for people developing dictator applications is to hide all the important sheets in the file using vba code in the Before save event & only leave one visible sheet that explains that macros must be enabled. This is explained well in http://datapigtechnologies.com/blog/index.php/forcing-your-clients-to-enable-macros/.

As you seem to know though, Excel's not designed as a secure application so there will always be some risks. To an extent, the risks you are facing can't ever be completely removed even with secure applications. Contractual obligations & penalties may help, but when it comes down to it - a bad egg will usually find a way to circumvent whatever measures are used.

Rob
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 39963578
Hi, given that you're not adverse to programming, I'm thinking the best approach is for you to build an EXE wrapper to your Excel file that would store the value of
HKCU\software\microsoft\office\xx.x\excel\security\Level
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.
0
 
LVL 10

Expert Comment

by:broro183
ID: 39963586
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-exchange.com/searchResults.jsp?searchTerms=digital+signature+in+excel+macros&searchSubmit=
http://office.microsoft.com/en-nz/excel-help/digitally-sign-a-macro-project-HA001231781.aspx
This was the most informative thread for me: http://www.mrexcel.com/forum/excel-questions/110793-digitally-signing-macros.html

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
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 10

Expert Comment

by:broro183
ID: 39963588
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!
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39963760
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.
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39964123
0
 

Author Closing Comment

by:Basicfarmer
ID: 39964175
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.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
My experience with Windows 10 over a one year period and suggestions for smooth operation
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now