vba - 如何防止共享点文件签入在关闭前触发工作簿？

Question

我的 sub Workbook_BeforeClose 运行了两次，因为在我的 Sub CloseWBFromSharePointFolder 中，我要么签入我的文件，要么丢弃它，要么取消并且什么都不做（参见下面的代码）。签入和丢弃文件都会触发 Workbook_BeforeClose 再次运行。

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    CloseWBFromSharePointFolder
End Sub

CloseWBFromSharePointFolder 中触发 Workbook_BeforeClose 的两个片段

报到

ActiveWorkbook.CheckIn SaveChanges:=True, Comments:="Checked-In by " & Application.Username

丢弃

Application.ActiveWorkbook.CheckIn False

任何帮助，将不胜感激。

Ps 我还尝试使用公共变量来跟踪它是否再次运行。这不起作用，因为公共变量已重置。我找到的解释是因为 Workbook_BeforeClose 调用了 CloseWBFromSharePointFolder，然后触发了 Workbook_BeforeClose。这将重置所有内容并且公共变量变为空

PS2了解更多详情。

CloseWBFromSharePoint 文件夹代码

Sub CloseWBFromSharePointFolder()
    Dim myForm1 As UserForm1

    Set myForm1 = UserForm1

    myForm1.Caption = "Choose before closing:"
    myForm1.Show
End Sub

UserForm1 代码

Dim Buttons() As New BtnClass

Private Sub UserForm_Initialize()
    Dim ButtonCount As Integer
    Dim ctl As Control

     ' Create the Button objects
     ButtonCount = 0
     For Each ctl In UserForm1.Controls
        If TypeName(ctl) = "CommandButton" Then
        'Skip the OKButton
            If ctl.Name <> "OKButton" Then
                ButtonCount = ButtonCount + 1
                ReDim Preserve Buttons(1 To ButtonCount)
                Set Buttons(ButtonCount).ButtonGroup = ctl
            End If
        End If
    Next ctl
    Me.CommandButton1.Caption = "Check in"
    Me.CommandButton2.Caption = "Discard check-out"
    Me.CommandButton3.Caption = "Keep checked-out"
    Me.CommandButton4.Caption = "Cancel"
End Sub

BtnClass 代码

Public WithEvents ButtonGroup As MsForms.CommandButton
Private Sub ButtonGroup_Click()

If UserForm1.Visible = True Then
    Select Case ButtonGroup.Name
        Case "CommandButton1" 'check in
            CheckIn
        Case "CommandButton2" 'Discard check-out
            Discard
        Case "CommandButton3" 'Keep checked-out
            KeepCheckedOut
        Case Else ' Cancel
            'Do Nothing
    End Select
    Unload UserForm1
ElseIf UserForm2.Visible = True Then
    Select Case ButtonGroup.Name
        Case "CommandButton1" 'check out
            CheckOut
        Case "CommandButton2" 'Read only
            'Do Nothing
        Case Else ' Cancel
            'Do Nothing
    End Select
    Unload UserForm2
End If
End Sub

Sub CheckIn()
    If ActiveWorkbook.CanCheckIn = True Then
        'Check In, Save and Close
        ActiveWorkbook.CheckIn SaveChanges:=True, Comments:="Checked-In by " & Application.Username
        MsgBox ("File sucessfully checked in")
    Else
        MsgBox ("File could not be checked in!")
    End If
End Sub

标签： vbasharepointevent-handlingcheckin