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.

 

[exl]

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.

Dear Roy,

Please help with the following in your workbook:

1. I have put my domain in the place designated as "your network here", but I am still getting the error that not on network. Are there any places that I am missing here.

2. Also, after i get the message that I am not on network, the "Macro" sheet gets hidden and the rest of the sheets are exposed, which I believe should only happen if he is in network.

Please take a look.

 

[royUK]

There's a line of code in the Open event that is commented out to close the workbook if the domain name is wrong. Remove the comment and the workbook will close without saving.

If Not NetWorkOK("your domain name here") Then
        MsgBox "Not opened on work network"
        '-> uncomment the next line the workbook will close if the domain name is notrecognised.
        '        ThisWorkbook.Close False

I've also checked through the code and amended it testing it using my PC name seems to work fine. I;ve updated the example workbook with the new code.

Force Enable Macros

 

[exl]

There's a line of code in the Open event that is commented out to close the workbook if the domain name is wrong. Remove the comment and the workbook will close without saving.

If Not NetWorkOK("your domain name here") Then
        MsgBox "Not opened on work network"
        '-> uncomment the next line the workbook will close if the domain name is notrecognised.
        '        ThisWorkbook.Close False

I've also checked through the code and amended it testing it using my PC name seems to work fine. I;ve updated the example workbook with the new code.

Force Enable Macros

Dear Roy,

Thanks a lot, your workbook solves almost all of my requirements, only I nagging problem is left.

In normal state, in my workbook there are 24 sheets, out of which 12 are 'Base Data Sheets' and are veryhidden and the rest 12 are dashboard type sheets, visible. But your macro reveals all of the sheets. Is it possible to keep the veryxlhidden sheets as they are?

 

[royUK]

You can make a slight change to not show very hidden sheets.

Option Explicit
Dim ws As Worksheet
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Unhide the Starting Sheet
    Sheets("Macros").Visible = xlSheetVisible
    ' Loop through all worksheets
    For Each ws In ThisWorkbook.Worksheets
        'Check each worksheet name, exclude warning sheet
        'Hide other sheets sheet
        If ws.Name <> "Macros" Then ws.Visible = xlHidden
    Next ws
    ' Save the workbook
    ThisWorkbook.Save


End Sub


Private Sub Workbook_Open()
'this code checks the domain, needs the function completing with your domain name
    If Not NetWorkOK("your domain name here") Then
        MsgBox "Not opened on work network"
        '-> uncomment the next line the workbook will close if the domain name is notrecognised.
        '        ThisWorkbook.Close False
    End If
    ' Loop through all worksheets
    For Each ws In ThisWorkbook.Worksheets
        'Unhide All Worksheets
        'exclude very hidden sheets
        If ws.Visible <> xlSheetVeryHidden Then ws.Visible = xlSheetVisible
        'Loop to next worksheet
    Next ws
    '  Hide the macro Sheet
    Sheets("Macros").Visible = xlVeryHidden
End Sub

The problem is that the other sheets can then be opened manually. To get round this you could exclude specific sheets which is probably the best way

Option Explicit
Dim ws As Worksheet
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Unhide the Starting Sheet
    Sheets("Macros").Visible = xlSheetVisible
    ' Loop through all worksheets
    For Each ws In ThisWorkbook.Worksheets
        'Check each worksheet name, exclude warning sheet
        'Hide other sheets sheet
        If ws.Name <> "Macros" Then ws.Visible = xlVeryHidden
    Next ws
    ' Save the workbook
    ThisWorkbook.Save


End Sub


Private Sub Workbook_Open()
'this code checks the domain, needs the function completing with your domain name
    If Not NetWorkOK("your domain name here") Then
        MsgBox "Not opened on work network"
        '-> uncomment the next line the workbook will close if the domain name is notrecognised.
        '        ThisWorkbook.Close False
    End If
    ' Loop through all worksheets
    For Each ws In ThisWorkbook.Worksheets
        'Unhide All Worksheets
        'exclude very hidden sheets
        Select Case ws.Name
        Case "A", "B", "C"    '<- change the names to list all your sheets that must remain hidden
            'do nothing
        Case Else    'unhide all other sheets
            ws.Visible = xlSheetVisible
        End Select
        'Loop to next worksheet
    Next ws
    '  Hide the macro Sheet
    Sheets("Macros").Visible = xlVeryHidden
End Sub

 

[exl]

Dear Roy,

I am a little lost, if you could indicate the exact position and way to change the worksheet, it would be great.

 

[exl]

Dear Roy,

I got it, finally it works...

I know I have bugged you a lot, but one last query:

1. I had planned to secure my workbook by both locking the VBA project with password and also protecting the workbook (Review > Protect Workbook), but I cannot implement the latter as it will not allow un-hiding of some sheets from the macro.

2. If I only lock the VBA project with password, all sheets can be un-hidden by add-ins like "ASAP Utilities"

Is there any way, or do I have to risk it and go with No.2

 

[exl]

Dear Roy & MMMarks,

Thank you very much for all the co-operation, I am so much obliged to you guys and this forum. Please let me know if there are any specific ways on this forum to highlight what a wonderful job you guys have done. I feel this thread should be bookmarked as it will come in handy for others.

Many thanks once again

 

[royUK]

If you password protect the workbook then you can unprotect it from within your code.

Workbooks("Whatever").Unprotect Password:="secret"
'run some code
Workbooks("Whatever").Protect Password:="secret"

There's a like button in the bottom right of each post

 

[exl]

If you password protect the workbook then you can unprotect it from within your code.

Workbooks("Whatever").Unprotect Password:="secret"
'run some code
Workbooks("Whatever").Protect Password:="secret"

There's a like button in the bottom right of each post

Dear Roy,

I have tried to incorporate the code as below, but when I close the workbook, I get a Run-time error '1004': Unable to set the visible property of worksheet class

I have added the code in the 'This Workbook' module:

Option Explicit
Dim ws As Worksheet
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Unhide the Starting Sheet
    Sheets("Macros").Visible = xlSheetVisible
    ' Loop through all worksheets
    For Each ws In ThisWorkbook.Worksheets
        'Check each worksheet name, exclude warning sheet
        'Hide other sheets sheet
        If ws.Name <> "Macros" Then ws.Visible = xlVeryHidden
    Next ws
    ' Save the workbook
    ThisWorkbook.Save

End Sub

Private Sub Workbook_Open()
'this code checks the domain, needs the function completing with your domain name
    If Not NetWorkOK("RoyCox-PC-Lenov") Then
        MsgBox "Not on Network"
        '-> uncomment the next line the workbook will close if the domain name is notrecognised.
        '        ThisWorkbook.Close False
    End If
    ' Loop through all worksheets
    Workbooks("Name").Unprotect Password:="Password"
'run some code

    For Each ws In ThisWorkbook.Worksheets
        'Unhide All Worksheets
        'exclude very hidden sheets
        Select Case ws.Name
        Case "Base", "Rate" '<- change the names to list all your sheets that must remain hidden
            'do nothing
        Case Else    'unhide all other sheets
            ws.Visible = xlSheetVisible
        End Select
        'Loop to next worksheet
    Next ws
    '  Hide the macro Sheet
    Sheets("Macros").Visible = xlVeryHidden
    Workbooks("Name").Protect Password:="Password"
End Sub

 

[royUK]

You must have at least one sheet visible.