This is a discussion on Restrict opening of an Excel outside the organization within the Excel Questions forums, part of the Question Forums category; For security reasons i would not store the domain name in a worksheet. i have hard coded it, but other ...
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 : 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.
@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 VBA project with password
2. Protect the workbook with a password
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 : 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.
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.
I am pretty close to getting this done, request you guys to help out in the last stage.
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.