Restrict opening of an Excel outside the organization

exl

Board Regular
Joined
Feb 4, 2010
Messages
153
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

Well-known Member
Joined
Jul 24, 2002
Messages
1,606
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!
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

mmmarks

Active Member
Joined
Jun 4, 2011
Messages
428
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

Board Regular
Joined
Feb 4, 2010
Messages
153
@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

Board Regular
Joined
Feb 4, 2010
Messages
153
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

Well-known Member
Joined
Jul 24, 2002
Messages
1,606

ADVERTISEMENT

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.
Code:
'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

Active Member
Joined
Jun 4, 2011
Messages
428
@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

Board Regular
Joined
Feb 4, 2010
Messages
153

ADVERTISEMENT

@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

Well-known Member
Joined
Oct 25, 2010
Messages
553
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

Board Regular
Joined
Feb 4, 2010
Messages
153
I am pretty close to getting this done, request you guys to help out in the last stage.
 

royUK

Well-known Member
Joined
Jul 24, 2002
Messages
1,606
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.
 

Forum statistics

Threads
1,144,281
Messages
5,723,471
Members
422,499
Latest member
think say

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top