Solved

Excel macro security

Posted on 2014-03-28
7
524 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
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 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

622 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