特定WB上的用户表单

Question

我在一个特定的WorkBook上有一个用户表单，它所做的是从用户那里收集数据，并将其转储到同一个WorkBook上。我的问题是，如果我最小化了WB，打开了另一个WB，然后我填写了用户表单，数据就不会转储到最小化的工作簿上。我不确定如何引用特定WorkBook，这样即使打开了其他WorkBook，它仍然会将我的数据转储到我想要的WorkBook上。这一切都有可能吗？如果是这样的话，是怎么做的？

下面是我的代码以供参考。

Private Sub cmdSubmit_Click()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

If ActiveWorkbook.MultiUserEditing Then
ActiveWorkbook.AcceptAllChanges
ActiveWorkbook.Save
End If

    If txtCallID.Value = "" Then
    MsgBox "Please enter a Call ID.", vbExclamation, "CALL ID FIELD ERROR"


'if all fields were answered, show Message Box for confirmation
    Else
        Dim response As Integer


        response = MsgBox("Please review all information before proceeding." & vbCrLf & "Click YES to Proceed, Click NO to review.", _
                vbYesNo + vbInformation, "Audit Tracker")

     End If
        If response = vbYes Then

    Dim lRow As Long
    Dim ws As Worksheet


    Set ws = Worksheets("Data Sheet")
    'find  first empty row in database
    lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row


    If WorksheetFunction.CountIf(ws.Range("E2", ws.Cells(lRow - 1, 1)), Me.txtCallID.Value) > 0 Then
        MsgBox "Somebody is already auditing this call", vbCritical, "Duplicate Call ID"
        Me.txtCallID.Value = ""
        Exit Sub
    End If



     'check for a segement id
    If Trim(Me.txtCallID.Value) = "" Then
        Me.txtCallID.SetFocus
        MsgBox "Please enter the Call ID"
        Exit Sub
    End If

     'copy the data to the database
     With ws
    .Cells(lRow, 1).Value = txtDate.Value
    .Cells(lRow, 3).Value = Environ$("username")
    .Cells(lRow, 5).Value = Me.txtCallID.Value


If ActiveWorkbook.MultiUserEditing Then
ActiveWorkbook.AcceptAllChanges
ActiveWorkbook.Save
End If

     'clear the data
    Me.txtCallID.Value = ""

    Me.txtCallID.SetFocus
   End With
   End If

Application.ScreenUpdating = True
Application.DisplayAlerts = True


End Sub

我希望做的是始终将数据转储到我的工作簿DataTracker.xlsd中

Answer 1

，这样即使打开了其他WorkBook，它仍然会将我的数据转储到我想要的WorkBook上

ThisWorkbook是指拥有窗体或正在执行的代码模块的工作簿。另一方面，ActiveWorkbook指的是Excel中当前处于活动状态的工作簿。要修复您的代码：

将ActiveWorkbook替换为ThisWorkbook。

还有：Set ws = Worksheets("Data Sheet")

作者：Set ws = ThisWorkbook.Worksheets("Data Sheet")

因为如果不指定ThisWorkbook，没有限定符的Worksheets函数将获取ActiveWorkbook的工作表(默认行为)，并且可能会失败。