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:
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
Could you provide a step-by-step for the following:
Copy this script into the code window of “ThisWorkbook”.
Where is the code window?