Restrict opening of an Excel outside the organization

[exl]

I have an excel which contains classified information. It does not contain any macro. I have put in workbook level security so employees cannot check the 'veryHiddenSheets'. I am ok with employees copying the data etc, but I don't want anybody outside the organization opening it. The excel workbook should only open within the organization.

I had seen a macro somewhere which checks the username and provides relevant data, can a similar strategy be used to check for a Domain or something (bcoz our logins is within a domain) and then provide access to the document. Eagerly awaiting some answers, else I won't be able to circulate the document.

 

[royUK]

For security reasons i would not store the domain name in a worksheet. i have hard coded it, but other options would be as a hidden named range or as a Custom Document property. personally I would go with the hard coded and protect the VBA Project.

Edit: nice code for the domain name!

 

[mmmarks]

For security reasons i would not store the domain name in a worksheet. i have hard coded it, but other options would be as a hidden named range or as a Custom Document property. personally I would go with the hard coded and protect the VBA Project.

Edit: nice code for the domain name!

Yeah. I hard coded the domain name in code ofcourse that will be second step to Protect the VBA project with password .

 

[exl]

@mmmarks : It works beautifully. Just a couple of aesthetics matter, if you can guide me. In the "Start" sheet, the domain names are appearing in cells A1 and B1, can we avoid that. If it can't be avoided can we change the cell address, say E5 and F5.

 

[exl]

For security reasons i would not store the domain name in a worksheet. i have hard coded it, but other options would be as a hidden named range or as a Custom Document property. personally I would go with the hard coded and protect the VBA Project.

Edit: nice code for the domain name!

@royUK : Thanks for your workbook too. I found it a little difficult to understand, but nonetheless I appreciate it. Would spend more time on getting to understand it. Maybe I can incorporate the domain name hardcoded if you can guide.

Also, I follow these steps to protect the workbook and codes:

1. Protect the <acronym title="visual basic for applications">VBA</acronym> project with password
2. Protect the workbook with a password

 

[royUK]

Sorry I didn't test the code before, I had to shoot off.

I have made some changes and tested it on my computer.

Use the same link and you will get the amended version.

'replace your domain name here with your domain name
    NetWorkOK = Right(network_and_computer, Len(network_and_computer) - InStr(1, _
                                                                              network_name, ".", vbTextCompare)) = "your domain name here"

 

[mmmarks]

@mmmarks : It works beautifully. Just a couple of aesthetics matter, if you can guide me. In the "Start" sheet, the domain names are appearing in cells A1 and B1, can we avoid that. If it can't be avoided can we change the cell address, say E5 and F5.

Yes. Cell reference can be changed to E5 and F5. Whereever you see A1 and B1 in code just Replace with E5 and F5 and color them in white so that others can't see it

 

[exl]

@mmmarks : Sorry to bother you again, one more thing we need to take care of. My workbook consists of 19 sheets, some of them are xlSheetVeryHidden and while others are visible. When a user opens the workbook on another domain, he is able to see all the Visible sheets (including the starting sheet).... This way even without 'Enabling the macro' he can view all the visible sheets. The 'Macro Effect' kicks in only after he enables it.

I can hide the sheet manually to xlSheetHidden, but the macro won't be able to unhide them as I have Workbook Protection. Is there any way that when a user opens the document (on any network), he sees only the first sheet, and if he is in the network then macro would only unhide the xlSheetHidden and not the xlSheetVeryHidden.

 

[energman58]

This is all great and I think what you need to do is hide all the WS except one which has a message saying "You need to enable Macros to use this file - close and reopen this file" using VeryHidden then in VBA use events:

Open - check the domain and if it matches unhide the worksheets you want the legitimate user to see and hide the "You need to enable Macros" sheet - if teh domain doesn't match show a message and close the file
BeforeClose and BeforeSave - hide all the WS and unhide the "You need to enable Macros" sheet

However WARNING (I) you need to stop the user simply stopping your code using CTRL-Break like this:

Prevent Users Ctrl Break (ing) Your VBA Code During Execution | Excel & VBA – Databison

WARNING (II) VBA/Excel is still going to be vulnerable to skilled people breaking in so I would not put anything super confidential in a VBA/Excel file.

Suggestion: as you are restricting people from opening the file who are not on your domain why not store the confidential stuff in a workbook on a shared drive somewhere and then link to it from the working sheets that you distribute: then security is done properly by managing the permissions on that drive and your sensitive data never leaves your system and users outside the domain will only see unresolved references - this is not totally foolproof but will stop many of the potenial leaks if you beef it up with some event handling to make sure that copies don't get saved easily.

 

[exl]

I am pretty close to getting this done, request you guys to help out in the last stage.

 

[royUK]

I am pretty close to getting this done, request you guys to help out in the last stage.

What do you need to do now/

My example hides all the worksheets unless macros are enabled.

It also uses the code mmarks domain code.

It does not store any passwords in the actual workbook for security. If you protect the VBA Project then that security is usually the most secure that Excel can provide, it can be cracked but most security can.