Page 2 of 5 FirstFirst 1234 ... LastLast
Results 11 to 20 of 43
Like Tree3Likes

Restrict opening of an Excel outside the organization

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 ...

  1. #11
    Board Regular
    Join Date
    Jul 2002
    Location
    Derbyshire, UK
    Posts
    1,589

    Default Re: Restrict opening of an Excel outside the organization

    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!
    Hope that helps.

    Free Excel sample DataBase Form here

    royUk's web site

    "making Excel work for you"

  2. #12
    Board Regular
    Join Date
    Jun 2011
    Location
    Hyderabad (India)
    Posts
    414

    Default Re: Restrict opening of an Excel outside the organization

    Quote Originally Posted by royUK View Post
    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 .

  3. #13
    exl
    exl is offline
    Board Regular
    Join Date
    Feb 2010
    Posts
    153

    Default Re: Restrict opening of an Excel outside the organization

    @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.

  4. #14
    exl
    exl is offline
    Board Regular
    Join Date
    Feb 2010
    Posts
    153

    Default Re: Restrict opening of an Excel outside the organization

    Quote Originally Posted by royUK View Post
    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 VBA project with password
    2. Protect the workbook with a password

  5. #15
    Board Regular
    Join Date
    Jul 2002
    Location
    Derbyshire, UK
    Posts
    1,589

    Default Re: Restrict opening of an Excel outside the organization

    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"
    Hope that helps.

    Free Excel sample DataBase Form here

    royUk's web site

    "making Excel work for you"

  6. #16
    Board Regular
    Join Date
    Jun 2011
    Location
    Hyderabad (India)
    Posts
    414

    Default Re: Restrict opening of an Excel outside the organization

    Quote Originally Posted by exl View Post
    @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

  7. #17
    exl
    exl is offline
    Board Regular
    Join Date
    Feb 2010
    Posts
    153

    Default Re: Restrict opening of an Excel outside the organization

    @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.

  8. #18
    Board Regular energman58's Avatar
    Join Date
    Oct 2010
    Location
    Muscat, Oman
    Posts
    553

    Default Re: Restrict opening of an Excel outside the organization

    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.

  9. #19
    exl
    exl is offline
    Board Regular
    Join Date
    Feb 2010
    Posts
    153

    Default Re: Restrict opening of an Excel outside the organization

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

  10. #20
    Board Regular
    Join Date
    Jul 2002
    Location
    Derbyshire, UK
    Posts
    1,589

    Default Re: Restrict opening of an Excel outside the organization

    Quote Originally Posted by exl View Post
    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.
    Hope that helps.

    Free Excel sample DataBase Form here

    royUk's web site

    "making Excel work for you"

Page 2 of 5 FirstFirst 1234 ... LastLast

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com