Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel macro security

Posted on 2014-03-28
7
Medium Priority
?
535 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 2000 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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 46

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 28

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Currently, there is an issue with being able to copy values from an external application to a dropdown list in Project Web Access (PWA).  The standard copy and paste methods don't seem to work properly. Here is a way to accomplish this task to s…
If Skype for Business came with your office 2016 or office 365 installation, you may find that it's almost impossible to either disable or remove it. The application will often launch with each start of Windows, even when explicitly configured not t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

578 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