How to protect the workbook from theft.

[NonStopLeo]

Hi Friends

Thanks to all of you for always helping me.

Here, I need an advice from you.

I have an excel workbook which has multiple sheets linked to each other with formulas and macros.

I want to share this workbook with my team for their use when they are on duty, but I want to secure this workbook in a way that If they leave my workplace, they can't copy, download or take this important workbook with them.

Can you please advice the possible solution to secure/protect my workbook from such cases?

Looking for your kind response.

Regards

 

[Kyle123]

It's not possible. There's no way you can stop them emailing the workbook to themselves and using it after they've left.

This is a contractual problem, not a technical one. The same issue exists even when you aren't using excel, staff can run reports, extract data and use it at their next job.

This is why you have covenants in contracts. They're very common in certain professions, particularly sales. But they're prevalent wherever you may have access to sensitive information - this is also why gardening leave exists

 

[Kamolga]

It is possible to block content to specific users, rather user account of excel: if office is licenced to firstname.lastname@yourorganisation.com, you could lock your file to users finishing by @yourorganisation.com and when they leave, that would limit access to people who have active office licence of your organisation.

this reference is

[COLOR=#000000][FONT=verdana]application.username [/FONT][/COLOR]

in VBA.

So something like

Private Sub Workbook_Open()If Not Application.UserName Like "*yourorganisation.com" Then
    ThisWorkbook.Close
End If
End Sub

when workbook is opening.

There is also a way to work with network username, I think through this

[COLOR=#000000][FONT=verdana]Public Declare Function GetUserName Lib "advapi32.dll" _[/FONT][/COLOR]
[COLOR=#000000][FONT=verdana]Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long[/FONT][/COLOR]

[COLOR=#000000][FONT=verdana]Function ReturnUserName() As String[/FONT][/COLOR]
[COLOR=#000000][FONT=verdana]' returns the NT Domain User Name[/FONT][/COLOR]
[COLOR=#000000][FONT=verdana]Dim rString As String * 255, sLen As Long, tString As String[/FONT][/COLOR]
[COLOR=#000000][FONT=verdana]tString = ""[/FONT][/COLOR]
[COLOR=#000000][FONT=verdana]On Error Resume Next[/FONT][/COLOR]
[COLOR=#000000][FONT=verdana]sLen = GetUserName(rString, 255)[/FONT][/COLOR]
[COLOR=#000000][FONT=verdana]sLen = InStr(1, rString, Chr(0))[/FONT][/COLOR]
[COLOR=#000000][FONT=verdana]If sLen > 0 Then[/FONT][/COLOR]
[COLOR=#000000][FONT=verdana]tString = Left(rString, sLen - 1)[/FONT][/COLOR]
[COLOR=#000000][FONT=verdana]Else[/FONT][/COLOR]
[COLOR=#000000][FONT=verdana]tString = rString[/FONT][/COLOR]
[COLOR=#000000][FONT=verdana]End If[/FONT][/COLOR]
[COLOR=#000000][FONT=verdana]On Error GoTo 0[/FONT][/COLOR]
[COLOR=#000000][FONT=verdana]ReturnUserName = UCase(Trim(tString))[/FONT][/COLOR]
[COLOR=#000000][FONT=verdana]End Function[/FONT][/COLOR]

Unfortunately security in excel (password) is basically very easy to by-pass. I think an opening macro can be block by pressing shift key (or anotherone) during start and its standard security tends to protect itself from macro for new files/not trusted sources.

So the best solution I believe would be to rely on a protected network where you can read but not download.

 

[NonStopLeo]

is it possible that we can develop an online portal where we can put the same sheet so our users can use it online?

Once they will leave us, we will block thier access to the portal?

 

[Kyle123]

Unfortunately, not if you’re using macros. You’d need to redevelop it as a web application

 

[Kamolga]

That really depends on your structure. Most organisations have a secured network, accessible through VPN where IT can give rights (Not read, read only, modify, download, etc.) depending on the folder where the file is.

Sharepoint I believe is a way to have proper user control but I don't remember if we could block download (I remember downloading and uploading files)

I would use access with different front ends if I needed a kind of application solution. Online portal is indeed the best in term of security, very easy to set up if you just want to read information from an excel table, otherwise this is quickly a lot of work.

 

[Yongle]

Starting with the negatives

Acknowledge that the security in Excel is poor and can be "worked around" if anyone is determined enough
- VBA is ineffective if macros are not enabled
Additionally, you need to consider the environment outside Excel
- the workbook can be attached to an email if the user has access to the folder
- the workbook can be copied and renamed within file manager
- ScreenPrints can be taken of worksheets with {Ctrl} {PrintScreen} and OCR is good these days
etc

But you can make life more difficult for anyone trying to get at your data
- consider examples below (some of which may not be appropriate to your situation)

VBA Project (if VBA is used) should itself be be password protected

Force user to enable macros
- save workbook with all sheets except one "very hidden"
- user cannot unhde "very hidden" sheets without VBA
- user cannot access other sheets without enabling VBA

Tie usability of the workbook to your system
- user names can be "managed" to make them less likely to match on any other computer
- the folder path where the file saved is specific to your system (tricky if user is downloading it for use - but there are other items which can be verified instead)
- use VBA to run a variety of unseen checks verifying your "unique" properties before unhiding other sheets
- password protecting anything is almost pointless if the password is provided to an untrustworthy user
- ask user for a password to give appearance of security but the unseen checks are more important as they tie usage of the file to your system

Use sheet and workbook protection
- protect workbook structure and worksheets (unlocking as above)
- sheets can be protected so that users cannot select multiple cells (prevents easy copying and pasting to another workbook)

Disable SaveAs (and possibly Save)
- VBA can be used to disable SaveAs (or limited to specific users etc)
- Save can also be disabled but this may be unhelpful

Control how user interacts with worksheet
- VBA can be used to tightly control what the user can do
- disable the "normal way" of doing certain things and use VBA to provide user with method over which you have control
- userforms can be used to capture and return data (prevents user getting to the worksheet itself)
- prevent user selecting multiple cells (copying and pasting to another workbook made more difficult)

Subdivide the sheets into different workbooks
- allow users access only to top level workbook which contains nothing but formula
- workbook returns errors without access to underlying workbook(s)
- perhaps it is a "transient" workbook which builds all values on Open and destroys values on Close
- a copy of such a workbook would be useless if it opened with a sheet full of errors
- this may be unacceptably onerous on system resources if the dataset is huge

Q1 Do users need to "view" or "amend"

Q2 Does everyone need access to all worksheets?

 

[NonStopLeo]

Thanks Mr. Yongle for the very detailed reply.
This is basically a workbook containing price matrix.
User enters all information on a sheet called “input” and then macros returns different pricing options from other sheets based on inputs.

 

[Yongle]

Precisely what are you trying to prevent the user copying and saving?
What data does the user require to be retained after pricing options returned?

One of my suggestions was splitting the workbook into separate workbooks
- (based on your reply) perhaps that could work for you
- one workbook for user input values
- user submits data (via macro)
- a different workbook (user has no direct access) called by the macro does the number-crunching
- macro returns pricing options to user

 

[NonStopLeo]

Thanks for the suggestion. Let me try to split the worksheet.

Regards