Excel access control?

Hi

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.

2 Answers

Relevance
  • 9 years ago
    Best 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)

    Else

    tString = rString

    End If

    On Error GoTo 0

    ReturnUserName = UCase(Trim(tString))

    End Function

    Sub Record_Access()

    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)

    Sheets("Access").Columns("A:C"). AutoFit

    ActiveWorkbook.Save

    End Sub

    Sub View_Access()

    If Sheets("Access").Visible = xlVeryHidden Then

    Sheets("Access").Visible = -1

    Sheets("Access").Activate

    Else

    Sheets("Access").Visible = 2

    End If

    End Sub

    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

    End If

    Next

    If ctr = 0 Then

    Sheets.Add

    ActiveSheet.Name = "Access"

    ActiveSheet.Range("A1").Value = "Date"

    ActiveSheet.Range("B1").Value = "Time"

    ActiveSheet.Range("C1").Value = "Accessed By"

    ActiveSheet.Visible = xlVeryHidden

    Record_Access

    Else

    Sheets("Access").Visible = 2

    Record_Access

    End If

    End Sub

    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.

  • 4 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.

Still have questions? Get your answers by asking now.