vba - 显示上次用户在 vba 中打开共享工作簿的时间

Question

我有一个共享的电子表格，可以由多个用户打开。我希望有一个单独的选项卡，其中包含电子表格的所有用户以及他们最后一次打开的时间。

首先这可能吗？我听说共享工作表存在限制，我对此有其他想法，但这种方式是最好的。

我还没有尝试过任何东西，因此为缺少代码而道歉，但如果有人能指出我正确的方向，我将不胜感激。

谢谢!

Answer 1

这是完全可能的。

将它放在工作簿模块中，然后手动运行 Workbook_Open 方法一次。

Option Explicit

Private Sub Workbook_Open()

    Dim sh As Worksheet
    Dim objList As ListObject
    Dim listRow As listRow

    Set sh = getSheet("TrackOpen")

    'Make the sheet if it doesnt already exist
    If sh Is Nothing Then
        Set sh = ThisWorkbook.Worksheets.Add
        sh.name = "TrackOpen"
    End If

    Set objList = getListObject("TBL_Logins")

    'Make the table if it doesn't already exist
    If objList Is Nothing Then
        Set objList = ThisWorkbook.Sheets("TrackOpen").ListObjects.Add
        With objList
            .name = "TBL_Logins"
            .ListColumns.Add
            .ListColumns(1).name = "User"
            .ListColumns(2).name = "Timestamp"
        End With
    End If

    Set listRow = objList.ListRows.Add
    With listRow
        .Range(1, objList.ListColumns("User").Index) = Environ("Username")
        .Range(1, objList.ListColumns("Timestamp").Index) = Now()
    End With

    'Optional, uncomment to save
    'ThisWorkbook.Save

End Sub

Private Function getSheet(sheetName As String) As Worksheet
    On Error GoTo uhoh
    Set getSheet = ThisWorkbook.Sheets(sheetName)
    Exit Function
uhoh:
    Set getSheet = Nothing
End Function

Private Function getListObject(listName As String) As ListObject
    Dim sh As Worksheet
    Dim lst As ListObject
    On Error GoTo uhoh
    For Each sh In ThisWorkbook.Sheets
        For Each lst In sh.ListObjects
            If lst.name = listName Then Set getListObject = lst: Exit Function
        Next lst
    Next sh
uhoh:
    Set getListObject = Nothing
End Function