Solved

Excel macro security

Posted on 2014-03-28
7
510 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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 27

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

770 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