Restrict opening of an Excel outside the organization

exl

Board Regular
Joined
Feb 4, 2010
Messages
153
Office Version
  1. 2019
Platform
  1. Windows
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.
 

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
If you want secure then Excel is not the right application.

If you did find a method then it would involve VBA, if it requires VBA then this can be simply bypassed by not enabling macros.

There's software available to lock your Excel application but they aren't cheap.
 
Upvote 0
If you want secure then Excel is not the right application.

If you did find a method then it would involve VBA, if it requires VBA then this can be simply bypassed by not enabling macros.

There's software available to lock your Excel application but they aren't cheap.

Some kind of security is better than none. I know VBA has its limitations, but if someone has a code which can check the domain. Also, to activate or view any sheets macro have to be enabled, else user will not to be able to view any data. If that kind of VBA code is there, then we can give it a try.

I am sure, this is a common requirement and there might be a standard solution.
 
Upvote 0
just dropping an idea.
As you said its limited to your organization so just take all Windows User names from everyone and put them at one place in excel (ofcourse you can make it invisible those names)
Write a Event Macro while opening Excel file ...
Its check whether the person who is trying to open this excel is belongs to your organization or not.

Logic : using this statement in VBA . "appliaction.username"

Username should match with one name which is already placed by you . If not excel will not open

Thaks
A
 
Upvote 0
just dropping an idea.
As you said its limited to your organization so just take all Windows User names from everyone and put them at one place in excel (ofcourse you can make it invisible those names)
Write a Event Macro while opening Excel file ...
Its check whether the person who is trying to open this excel is belongs to your organization or not.

Logic : using this statement in VBA . "appliaction.username"

Username should match with one name which is already placed by you . If not excel will not open

Thaks
A

Thanks for the idea, but user base keeps on changing, people come and leave, so it would be a hassle. Can we check the domain??
 
Upvote 0
Hi,
Below is the code to get Domain and Username . Place below code in Module and ran it.

make changes to according to your requirement.


Code:
Option Explicit

Enum COMPUTER_NAME_FORMAT
    ComputerNameNetBIOS
    ComputerNameDnsHostname
    ComputerNameDnsDomain
    ComputerNameDnsFullyQualified
    ComputerNamePhysicalNetBIOS
    ComputerNamePhysicalDnsHostname
    ComputerNamePhysicalDnsDomain
    ComputerNamePhysicalDnsFullyQualified
End Enum

Declare Function GetComputerNameEx Lib "kernel32" Alias "GetComputerNameExA" ( _
ByVal NameType As COMPUTER_NAME_FORMAT, _
ByVal lpBuffer As String, _
ByRef lpnSize As Long) As Long

Sub test()

Dim buffer As String
Dim size As Long
Dim network_and_computer As String
Dim network_name As String

size = 255
buffer = Space(size)
GetComputerNameEx ComputerNameDnsFullyQualified, buffer, size
network_and_computer = Left$(buffer, size)

MsgBox network_and_computer

network_name = Right(network_and_computer, Len(network_and_computer) - InStr(1, network_and_computer, ".", vbTextCompare))

MsgBox network_name

End Sub
 
Upvote 0
@mmmarks : Wow, it is throwing up the domain name when running the macro. The first message box gives the Computer name and the second one gives the domain.

Now I got to be frank here, I do not have good knowledge of VBA, I mean I know how to use it and stuff, but I can't write it or edit it much. Hope you would help me out.

Could you help me achieve the following:

1. On opening the workbook, if the user does not enable macro, then he doesn't see the data. In one way we can provide a sheet, with instructions that user must enable macros to view the data, all other sheets hidden. The sheets will only become visible if he enables the macro.

2. When he enables the macro, your above macro should start immediately, and it should check if the domain name we specify inside the macro, matches with the one on the computer of the user. If it matches, all sheets will become unhidden, else a message box can tell the user that he does not have access.

I know I am asking too much here, but I really need this. I have always looked forward to this forum as the final word for all my excel problems.
 
Upvote 0
I have made you an example using mmmark's code

ForceEnablemacros

You will see a Function based on the code that checks the domain. Complete that by adding your domain name where indicated.

The code also contains simple code to hide all worksheets except a warning sheet until macros are enabled.
 
Upvote 0
Hi
Below is my approach.

place below code in Module :
Code:
Option Explicit

Enum COMPUTER_NAME_FORMAT
    ComputerNameNetBIOS
    ComputerNameDnsHostname
    ComputerNameDnsDomain
    ComputerNameDnsFullyQualified
    ComputerNamePhysicalNetBIOS
    ComputerNamePhysicalDnsHostname
    ComputerNamePhysicalDnsDomain
    ComputerNamePhysicalDnsFullyQualified
End Enum

Declare Function GetComputerNameEx Lib "kernel32" Alias "GetComputerNameExA" ( _
ByVal NameType As COMPUTER_NAME_FORMAT, _
ByVal lpBuffer As String, _
ByRef lpnSize As Long) As Long

Sub test()

Dim buffer As String
Dim size As Long
Dim network_and_computer As String
Dim network_name As String

size = 255
buffer = Space(size)
GetComputerNameEx ComputerNameDnsFullyQualified, buffer, size
network_and_computer = Left$(buffer, size)

network_name = Right(network_and_computer, Len(network_and_computer) - InStr(1, network_and_computer, ".", vbTextCompare))


Worksheets(1).Range("A1") = network_name 'store in this domain name in unused cell

End Sub

Sub proc()
Dim i As Integer
Dim cnt As Integer
Worksheets(1).Range("B1").Value = "xxxx" 'enter your domain here make sure to keep this domain name in unused cell

If Range("B1").Value <> Range("A1").Value Then 'change B1 cell respectively

    cnt = Worksheets.Count
            For i = 2 To cnt
                ActiveWorkbook.Sheets(i).Visible = xlSheetVeryHidden
            Next i
    MsgBox "Sorry You don't have permission to access this file"
Else
            cnt = Worksheets.Count
            For i = 2 To cnt
                ActiveWorkbook.Sheets(i).Visible = xlSheetVisible
            Next i
End If

End Sub

In VBA Environment, Double Click on ThisWorkbook and place below code : below code will call while opening Excel workbook
Code:
Private Sub Workbook_Open()
Run "test"

Run "proc"


End Sub

FYI, I have commented few lines make sure to change those cell reference . and leave one Empty sheet since we cant hide all worksheets in workbook there should be ATLEAST ONE worksheet visible. so keep Sheet1 Empty.

Thanks
A
 
Upvote 0
hi,
this snippet below msgbox in PROC macro

Rich (BB code):
MsgBox "Sorry You don't have permission to access this file"
        If vbOK Then
            Application.DisplayAlerts = False
            Application.ThisWorkbook.Close False
         End If
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

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
Back
Top