Protecting Excel Workbook Information

[EvanJones2]

Hi guys. Thank you in advance.

So I created a workbook that houses my companies pricing information. I built it using VBA and is used by our sales staff to price out jobs company wide. Right now we just email updated versions of the software to the salesman and they keep it saved on their computers. But we have decided that the pricing information and VBA program needs to be kept within our own company. We're afraid someone will take this with them if they move onto another company.

So my question is, Is there a way that we can set up an admin password in excel. So after awhile the password would time out and lock the salesman out until we reset the password. Or does anyone else know of a program that we could attach that would require a password that we could control.

Please help me out. Any suggestion will be very helpful. Thank you!

[hrlngrv]

There are products on the market which can make VBA modules unviewable, but I haven't used any of them. There's no robust way to protect formulas and worksheet data in any workbook users can open. If you find a reliable product for protecting VBA modules, you could use one UDF which does nothing but return a default Variant, which has the VBA value Empty and is treated like the value of a truly blank cell in cell formulas.

Function q() As Variant: End Function

Change all your formulas to =original_numeric_formula+q() or =original_text_formula&q(). Then if users open the workbook with macros disabled, all formulas calling q() will return #NAME? errors. You could add warning messages like =IF(ISERROR(q()),"YOU MUST OPEN THIS WORKBOOK WITH MACROS ENABLED!","") to inform your users that they have to enable macros to be able to use your workbook. Then you can add user authentication to Workbook_Open. If authentication fails, that event handler closes the workbook or perhaps clears all data from the workbook and saves/overwrites itself first then closes.

Thing is you have to be able to protect VBA modules and you have to force users to open your workbook with macros enabled.