如何在将活动用户数据输入到工作表之前更新共享工作簿并更改其他工作簿？

I have a shared macro-enabled workbook used to conduct quiz. I have the option to configure users that can access the quiz with the help of computer names. Everything is working fine. However, I would like to introduce a feature to log major things done in the workbook. User does not enter anything manually into cells. All entries are made using userforms.

我有一个共享的宏启用工作簿用于进行测验。我可以选择配置可以借助计算机名称访问测验的用户。一切都很好。但是，我想介绍一个功能来记录工作簿中的主要内容。用户不会手动输入任何内容。所有条目都使用userforms进行。

I have made a sub, which runs every time user performs an operation that I need to capture. The code works like a charm when a single user is active. The problem occurs when multiple users are accessing the file at the same time.

我创建了一个sub，每次用户执行我需要捕获的操作时都会运行。当单个用户处于活动状态时，代码就像魅力一样。当多个用户同时访问该文件时，会发生此问题。

The problem is, user is shown a prompt to choose which change to win on closing the sheet. I don’t want this to happen. Is there anyway by which the prompt can be removed and make all changes saved.

问题是，用户会看到提示选择在关闭工作表时赢得哪个更改。我不希望这种情况发生。无论如何，可以删除提示并保存所有更改。

Is there any workaround for this. Below is the code that I have now.

有没有解决方法呢？下面是我现在的代码。

Sub Actions()
    QA_Quiz_Master.Save
    Application.ScreenUpdating = False
    UN = Evaluate("=UserNameWindows()")
    QuizN = Sheet4.Range("F2").Value
    totlog = Log.Range("A1048576").End(xlUp).Row + 1
    Select Case Action
        Case "Open"
            Log.Range("C" & totlog).Value = "Accessed"
        Case "Start"
            Log.Range("C" & totlog).Value = "Started Quiz"
        Case "Submit"
            Log.Range("C" & totlog).Value = "Submitted Quiz"
        Case "AdminContact"
            Log.Range("C" & totlog).Value = "Contacted Admin"
        Case "AccessRequest"
            Log.Range("C" & totlog).Value = "Sent Access Request"
        Case "Publish"
            Log.Range("C" & totlog).Value = "Published Quiz"
        Case "Republish"
            Log.Range("C" & totlog).Value = "Republished Quiz"
        Case "Withdraw"
            Log.Range("C" & totlog).Value = "Withdrew Quiz"
        Case "AnsPublish"
            Log.Range("C" & totlog).Value = "Published Answers"
    End Select
    Log.Range("A" & totlog).Value = UN
    Log.Range("B" & totlog).Value = QuizN
    Log.Range("D" & totlog).Value = Now()
    Log.Columns("A:D").EntireColumn.AutoFit
    Application.DisplayAlerts = False
    QA_Quiz_Master.Save
    Application.ScreenUpdating = True
End Sub

3 个解决方案

#1

After some research, I myself got the problem solved. I just needed to add the following code at the beginning of the sub

经过一番研究，我自己解决了问题。我只需要在sub的开头添加以下代码

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

#2

When you are setting the workbook to Shared, look at the Advanced tab. By default it updates changes when the file is saved but you can choose to have it automatically update changes every x minutes. You can also choose if you want Excel to ask which changes win or if you want to automatically save the new changes.

将工作簿设置为“共享”时，请查看“高级”选项卡。默认情况下，它会在保存文件时更新更改，但您可以选择让它每隔x分钟自动更新更改。您还可以选择是否希望Excel询问哪些更改获胜，或者是否要自动保存新更改。

#3

Shared Workbooks are a nightmare! One should always avoid using them. I realized this when I was working as an Ops Manager couple of years back. I wanted my managers to update a shared Report at the end of the day and trust me it was a disaster.

共享工作簿是一场噩梦！人们应该总是避免使用它们。几年前，当我作为Ops经理工作时，我意识到了这一点。我希望我的经理们在一天结束时更新共享报告并相信我这是一场灾难。

Here is an alternative.

这是另一种选择。

Use Excel Userform as a Front End (i.e if you need to use Excel) and use Access Database as a Back End. If Excel is not a concern then port everything to MS Access. I see that you are conducting a quiz here. MS Access is a much better option here.

使用Excel Userform作为前端（即，如果您需要使用Excel）并使用Access数据库作为后端。如果Excel不是问题，那么将所有内容移植到MS Access。我看到你在这里进行测验。 MS Access是一个更好的选择。

If you want to pursue the above idea then do let me know and I can explain it in detail :)

如果你想追求上述想法，请告诉我，我可以详细解释:)

The other alternatives that I have used to collect data is displaying a webpage on the INTRANET and storing the results in Access/SQL database.

我用于收集数据的其他替代方法是在INTRANET上显示网页并将结果存储在Access / SQL数据库中。

#1