在初始化抛出错误时关闭表单

I'm trying to make a standardized process for updating a particular worksheet. I want no user control except for the functions I give them. To do that I have locked sheets and then forms that load with certain macros. One form is designed to remove data from the sheet. It works fine as written and tested, but I've tried to update it so that if you open it without any relevant data to remove, it spits out a dialogue box and then uses Unload Me to close the form. This closes the form but then excel throws an error:

我正在尝试制作更新特定工作表的标准化流程。除了我给他们的功能外,我不需要用户控制。为此,我已锁定工作表,然后加载某些宏的表单。一个表单旨在从工作表中删除数据。它编写和测试工作正常,但我试图更新它,以便如果您打开它没有任何相关数据要删除,它会吐出一个对话框,然后使用卸载我关闭表单。这会关闭表单但是然后excel会抛出一个错误:

Run-time error '91': Object variable or With block variable not set

运行时错误'91':对象变量或未设置块变量

The form is loaded from a module that only has the one line:

表单从只有一行的模块加载:

MyForm.Show

This is where excel is throwing the error from. On initialization of the form, a combobox is filled with values based on the data in the sheet. If the combobox is empty after loading, the form is supposed to throw the dialogue box and then close.

这就是excel抛出错误的地方。在对表单进行初始化时,组合框将根据工作表中的数据填充值。如果加载后组合框是空的,表单应该抛出对话框然后关闭。

If ComboBox.ListCount = 0 Then
    MsgBox "No Data"
    Unload Me
End If

How can I perform the check on load without having the error thrown from the Module?

如何在不从模块中抛出错误的情况下执行负载检查?

3 个解决方案

#1

This doesn't actually answer your question. But what I suggest is do the checking in your module code before you actually load the form. Something like:

这实际上并没有回答你的问题。但我建议在实际加载表单之前检查模块代码。就像是:

Sub LoadForm()
    If Sheets("Sheet1").Range("A1") = "" Then '<~~ your condition here
        MsgBox "No Data"
    Else
        MyForm.Show
    End If
End Sub

#2

Another way would be to place the Unload Me in the Activate event:

另一种方法是将Unload Me置于Activate事件中:

Private Sub UserForm_Activate()
    ...
    If ComboBox.ListCount = 0 Then
       MsgBox "No Data"
       Unload Me
    End If
End Sub

#3

The problem happens when you try and unload the userform from within it's initialize event. Because the object hasn't finished initialization yet, it cant be unloaded. The best ways to get around this are either to check conditions before you try to initialize the form, or put your checks and subsequent unload statements into the activate event of the userform. Activate is called whenever the form goes from being hidden to visible, which happens after the form has been completely initialized.

当您尝试从其初始化事件中卸载userform时,会发生此问题。因为对象尚未完成初始化,所以无法卸载。解决此问题的最佳方法是在尝试初始化表单之前检查条件,或者将检查和后续卸载语句放入userform的activate事件中。只要表单从隐藏变为可见,就会调用Activate,这在表单完全初始化后发生。

#1