vba 如何在活动用户数据输入工作表之前使用其他人的更改更新共享工作簿？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9014117/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to update a shared workbook with changes from others before active user data is entered into the sheet?
提问by Kannan Suresh
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.
我有一个用于进行测验的共享启用宏的工作簿。我可以选择配置可以在计算机名称的帮助下访问测验的用户。一切正常。但是，我想介绍一个功能来记录工作簿中完成的主要事情。用户不会在单元格中手动输入任何内容。所有条目都是使用用户表单进行的。
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.
我制作了一个子程序，每次用户执行我需要捕获的操作时它都会运行。当单个用户处于活动状态时，代码就像一个魅力。当多个用户同时访问文件时会出现此问题。
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
回答by Kannan Suresh
After some research, I myself got the problem solved. I just needed to add the following code at the beginning of the sub
经过一番研究，我自己解决了问题。我只需要在子的开头添加以下代码
If ActiveWorkbook.MultiUserEditing Then
QA_Quiz_Master.AcceptAllChanges
QA_Quiz_Master.Save
End If
回答by mischab1
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 询问哪些更改获胜，或者是否要自动保存新更改。
回答by Siddharth Rout
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.
共享工作簿是一场噩梦！人们应该始终避免使用它们。几年前，当我担任运营经理时，我意识到了这一点。我希望我的经理在一天结束时更新一份共享报告，相信我这是一场灾难。
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 用户窗体作为前端（即，如果您需要使用 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 数据库中。
