How to Protect Your Excel Files from Unauthorized Users

Excel workbook is used worldwide. Many use it as a simple database to hold data whereas several others use it as calculators or as a reporting tool. Somehow Excel carries our work and it is very important to protect these workbooks. Learn how to protect your Excel workbooks so that your critical data is always safe.

Download Now

If you want to start to use the software as soon as possible, then you can:

Download the Software Now

Otherwise, if you want to DIY, you can read the contents below.

Let’s Prepare the GUI

You can add protection to any of your workbooks by just creating the sheet “ControlPanel” sheet. The sheet should have fields “Usernames”,”Domain”,”Computername” and “IP”

Let’s prepare the database

Add values on the sheet “ControlPanel”. When a user opens the workbook, the Username or the domain or the computer name or the IP will be checked with this sheet.

Let’s make it functional

Copy this script into the code window of “ThisWorkbook”.

Private Sub Workbook_Open()
'Call Module1.Check_Username
'Call Module1.Check_Computer
    Call Module1.Check_Domain
'Call Module1.Check_IP
End Sub

Copy this script into the new module in your macro enabled workbook.

 Function f_getip()
    Const v_comp As String = "."
    Dim v_wmi, v_ipset, v_iconfig, v_ip, i
    Dim v_strip As String
    Set v_wmi = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & v_comp & "\root\cimv2")
    Set v_ipset = v_wmi.ExecQuery("Select * from Win32_NetworkAdapterConfiguration Where IPEnabled=TRUE")
    For Each v_iconfig In v_ipset
        v_ip = v_iconfig.IPAddress
        If Not IsNull(v_ip) Then
            v_strip = v_strip & Join(v_ip, ", ")
        End If
    Next
    f_getip = v_strip
End Function

Sub Check_Username()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Dim lr As Long
    Dim r As Long
    Dim v_allow As Boolean
    Dim v_username As String
    v_username = Environ$("UserName")
    v_allow = False
    lr = cp.Range("A" & Rows.Count).End(xlUp).Row
    For r = 2 To lr
        If cp.Range("A" & r).Value = v_username Then
            v_allow = True
            Exit For
        End If
    Next r
    If v_allow = False Then
        ThisWorkbook.Close False
    End If
End Sub

Sub Check_Domain()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Dim lr As Long
    Dim r As Long
    Dim v_allow As Boolean
    Dim v_domain As String
    v_domain = Environ("USERDNSDOMAIN")
    v_allow = False
    lr = cp.Range("B" & Rows.Count).End(xlUp).Row
    For r = 2 To lr
        If cp.Range("B" & r).Value = v_domain Then
            v_allow = True
            Exit For
        End If
    Next r
    If v_allow = False Then
        ThisWorkbook.Close False
    End If
End Sub

Sub Check_Computer()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Dim lr As Long
    Dim r As Long
    Dim v_allow As Boolean
    Dim v_computer As String
    v_computer = Environ("COMPUTERNAME")
    v_allow = False
    lr = cp.Range("C" & Rows.Count).End(xlUp).Row
    For r = 2 To lr
        If cp.Range("C" & r).Value = v_computer Then
            v_allow = True
            Exit For
        End If
    Next r
    If v_allow = False Then
        ThisWorkbook.Close False
    End If
End Sub

Sub Check_IP()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Dim lr As Long
    Dim r As Long
    Dim v_allow As Boolean
    Dim v_ip As String
    v_ip = f_getip
    v_allow = False
    lr = cp.Range("D" & Rows.Count).End(xlUp).Row
    For r = 2 To lr
        If cp.Range("D" & r).Value = v_ip Then
            v_allow = True
            Exit For
        End If
    Next r
    If v_allow = False Then
        ThisWorkbook.Close False
    End If
End Sub

How does it work?

When the workbook is opened, the macro runs immediately and check for username or domain name or ip or computer name. If there is non-compliance, the workbook is closed abruptly without any notification to the user.

Forced close

On a corrupt Excel file, while a macro is executed it might stop the process and close the file abruptly. Code incompatibility can also be a reason. Modify the code as per the version of Excel and it will solve the issue.

Author Introduction:

Nick Vipond is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including Word corruption and outlook recovery software products. For more information visit www.datanumen.com