Excel access control?
I work on a network with everyone logging with unique usernames and passwords. I normally distribute work through work through password protected shared excel files.
However i ned to create a macro to check who is accessing the file. When anyone access this excel file, i need excel to send me an email saying the user "<username> has viewed your file".
OR, his username should be captured on an other excel file with time and his user name.
- garbo7441Lv 79 years agoBest Answer
Here is a method to log user access within the same workbook, hidden from users but accessible with a keyboard shortcut.
The following event handler, function, and macro will perform the following tasks:
The first time the workbook is opened after adding the routines, a new worksheet named 'Access' will be created.
The new worksheet will be coded as 'xlVeryHidden', which means it is not available to be unhidden via the Format > Sheets > Unhide method.
Each time the workbook is opened thereafter, the current date, current time, and PC username will be added to the next available row in the 'Access' worksheet and the workbook will be immediately saved. This will capture the access entry, whether or not the user subsequently 'saves changes' upon closing the workbook.
To set up this process you must first remove shared access from the workbook. After adding the following routines, you can once again 'share' the workbook.
With the workbook open, copy the following function and macros to the clipboard:
Public Declare Function GetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Function ReturnUserName() As String
' returns the NT Domain User Name
Dim rString As String * 255, sLen As Long, tString As String
tString = ""
On Error Resume Next
sLen = GetUserName(rString, 255)
sLen = InStr(1, rString, Chr(0))
If sLen > 0 Then
tString = Left(rString, sLen - 1)
tString = rString
On Error GoTo 0
ReturnUserName = UCase(Trim(tString))
Dim uName As String
uName = ReturnUserName
Sheets("Access").Range("A" & Rows.Count). End(xlUp).Offset(1).Value = DateValue(Date)
Sheets("Access").Range("B" & Rows.Count). End(xlUp).Offset(1).Value = TimeValue(Time)
Sheets("Access").Range("C" & Rows.Count). End(xlUp).Offset(1).Value = Application.Proper(uName)
If Sheets("Access").Visible = xlVeryHidden Then
Sheets("Access").Visible = -1
Sheets("Access").Visible = 2
Press ALT + F11
In the menus at the top of the VBE, select INSERT > MODULE
Paste the code into the editing area to the right.
Still in the VBE, double click 'THIS WORKBOOK' in the Microsoft Excel Objects in the upper left quadrant.
Copy the following event handler into the workbook module editing area to the right:
Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In Sheets
If ws.Name = "Access" Then
ctr = 1
If ctr = 0 Then
ActiveSheet.Name = "Access"
ActiveSheet.Range("A1").Value = "Date"
ActiveSheet.Range("B1").Value = "Time"
ActiveSheet.Range("C1").Value = "Accessed By"
ActiveSheet.Visible = xlVeryHidden
Sheets("Access").Visible = 2
Close the VBE and return to the worksheet.
Press ALT + F8
When the Macros window opens, highlight the 'View_Access' macro and click 'Options...'
Enter a letter to be used as a keyboard shortcut. It can be an uppercase letter, which I suggest you use to further key the macro from prying eyes. Perhaps, use the uppercase 'V' as your shortcut letter.
Once the letter is entered, click 'OK'.
Close the Macros window.
Save the workbook.
Reopen the workbook and 'share' it.
To view the 'accessed by' log at any time, simply press CTRL + V (or your shortcut letter). To hide the 'Access' sheet, simply press CTRL + V (or your shortcut letter) again.
Note: I am not able to test this on a network server, but it functions appropriately 'standalone'. You also might want to password protect the Visual Basic Editor.
- AnnetteLv 44 years ago
Take a look at using Pivot Tables. Pivot Tables are very good at summarizing massive amounts of information while keeping the data source simple. Th help file has some very good examples that should give you some ideas on how to handle your task.