在 vba 运行时冻结 excel 编辑
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14903882/
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
Freeze excel editing while vba is running
提问by steventnorris
I have an excel sheet that I want to edit via vba script, but I need the users to not be able to edit any cells or click any buttons while the script is running. Due to a programs that I Shell after the excel button click, editing is enabled during this VBA sub run. What I need is a way to programmaticaly lock all cells for editing except by VBA. How can I do this?
我有一个想要通过 vba 脚本编辑的 Excel 工作表，但我需要用户在脚本运行时不能编辑任何单元格或单击任何按钮。由于我在单击 excel 按钮后脱壳的程序，在此 VBA 子运行期间启用了编辑。我需要的是一种以编程方式锁定除 VBA 之外的所有单元格进行编辑的方法。我怎样才能做到这一点？
Psuedo_Code:
伪代码：
Shell _ "PROGRAM1"
Shell _ "PROGRAM2"
Dim shellobject As PROGRAM2
shellobject.Connect "PROGRAM1"
shellobject.dothings
if(shellobject.success) then
Cells(1,1).Value = "HUZZAH!"
Else
Cells(1,1).Value = "OH NO MR BILL!"
End If
shellobject.dootherthings
etc.....
回答by even-steven
Have you considering disabling input from the keyboard. This could be a brute force way of accomplishing what you need but it's a thought.
您是否考虑过禁用键盘输入。这可能是完成您需要的蛮力方式，但这是一个想法。
You can use
您可以使用
Application.DataEntryMode =
to disable input from the keyboard. Set it to off once your script starts and turn it back on right before your script ends.
禁用键盘输入。脚本启动后将其设置为关闭，并在脚本结束前将其重新打开。
Again, this could be considered brute force but just an idea.
同样，这可以被认为是蛮力，但只是一个想法。
Hope that helps.
希望有帮助。
回答by Peter Albert
As you are using a UserForm, set it to be modalin the Properties dialog of the form (press F4in case it is not visible):
当您使用用户窗体时，在窗体的属性对话框中将其设置为模态（F4如果不可见，请按）：
This way, the user cannot click in Excel outside the form unless it is closed.
这样，除非窗体关闭，否则用户无法在窗体外单击 Excel。
Also, you can try to disable the buttons during the execution. This code should do that:
此外，您可以尝试在执行期间禁用按钮。这段代码应该这样做：
Private Sub CommandButton1_Click()
SetButtonsEnabled False
Application.EnableEvents = False
'Your code here
SetButtonsEnabled
Application.EnableEvents = True
End Sub
Private Sub SetButtonsEnabled(Optional blnEnable As Boolean = True)
Dim btn As Control
For Each btn In Me.Controls
If TypeOf btn Is CommandButton Then btn.Enabled = blnEnable
Next
End Sub
回答by Manoj
Following worked for me:
以下对我来说有效：
Use Application.Interactive = Falseat the beginning and re-enable it at the end. This will disable user clicks/entry attempts until the Macro/Userform/any code part completes its operation.
Application.Interactive = False在开始时使用并在最后重新启用它。这将禁用用户点击/输入尝试，直到宏/用户窗体/任何代码部分完成其操作。
回答by Tariq Khalaf
I was trying to comment to Steventnorris and Even-Steven but I am not able to, so just putting an answer here.
我试图对 Steventnorris 和 Even-Steven 发表评论，但我无法发表评论，所以只是在这里给出一个答案。
Application.DataEntryMode= xlOff in the beginning should be set to
Application.DataEntryMode= xlOn
and then at the end of the code
put in
Application.DataEntryMode= xlOff
I just tried this and it has stopped me from editing the sheet so in case anyone else is trying.
我刚试过这个，它阻止了我编辑工作表，以防其他人正在尝试。
