Restrict opening of an Excel outside the organization

royUK

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

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

mmmarks

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

Watch MrExcel Video

Forum statistics

Threads
1,095,141
Messages
5,442,631
Members
405,188
Latest member
maluenmaluen19

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top