在vba运行时冻结Excel编辑 [英] Freeze excel editing while vba is running

问题描述

我有一个要通过vba脚本编辑的Excel工作表，但是我需要用户在脚本运行时不能编辑任何单元格或单击任何按钮.由于单击excel按钮后我会执行Shell程序，因此在此VBA子运行期间启用了编辑.我需要的是一种以编程方式锁定除VBA以外的所有单元格以进行编辑的方法.我该怎么办?

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?

伪代码:

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.....

推荐答案

在使用用户窗体时，在窗体的属性"对话框中将其设置为 modal (按 F4 (如果看不到):

As you are using a UserForm, set it to be modal in the Properties dialog of the form (press F4 in case it is not visible):

这样，除非关闭窗体，否则用户无法在窗体外部单击Excel.

This way, the user cannot click in Excel outside the form unless it is closed.

此外，您可以尝试在执行过程中禁用按钮.这段代码应该做到这一点:

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

这篇关于在vba运行时冻结Excel编辑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！