Excel VBA:如何从代码触发事件?

I have a Worksheet_BeforeDoubleClick event that opens a listbox in the target cell. I was asked to provide the same functionality via a button instead of (or in addition to) the double-click.

我有一个Worksheet_BeforeDoubleClick事件，它在目标单元格中打开一个列表框。我被要求通过一个按钮提供相同的功能，而不是双击(或添加)。

In the button's Click event I entered:

在按钮的点击事件中，我输入:

Call Worksheet_BeforeDoubleClick(Selection,true)

...so the button simply "doubleclicks" the cell. It seems to work well, but before I start using this technique throughout my project, I'd like to know if there are pitfalls I should be aware of.

…因此，这个按钮只是“双击”单元格。它似乎运行得很好，但是在我开始在整个项目中使用此技术之前，我想知道是否存在我应该注意的缺陷。

What are the best practices when calling an event, either from another event or from a standard code module?

在调用事件(来自另一个事件或来自标准代码模块)时，最佳实践是什么?

1 个解决方案

#1

I'd like to know if there are pitfalls I should be aware of.

我想知道是否有我应该注意的缺陷。

Yes there is one major pitfall. The Selection necessarily might not be a range. See this example

是的，有一个主要的陷阱。选择不一定是一个范围。看这个例子

1. Insert a button

   插入一个按钮

2. Insert a blank chart

   插入一个空白的表

3. Insert an image in the chart. Let the image be highlighted

   在图表中插入一个图像。让图像突出显示

Let's say we have this code

假设我们有这个代码

Private Sub CommandButton1_Click()
    Call Worksheet_BeforeDoubleClick(Selection, True)
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    MsgBox Target.Address
End Sub

Now press the button and you will get an error.

现在按下这个按钮就会出现错误。

The worksheet events WILL fire when they NEED too... They won't when the selection is not appropriate.

工作表事件在需要时也会触发……当选择不合适时，他们不会这么做。

Having said that you CAN make your command button code work like this

说到这里，您可以让命令按钮代码像这样工作

Private Sub CommandButton1_Click()
    '~~> Check if what the user selected is a valid range
    If TypeName(Selection) = "Range" Then
        Call Worksheet_BeforeDoubleClick(Selection, True)
    Else
        MsgBox "Not a Valid Range"
    End If
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    MsgBox Target.Address
End Sub

But then where all and what all CHECKS will you place :) The best way is to place it in a Sub as @Sam suggested and then call it either from Button/Worksheet_BeforeDoubleClick.

最好的方法是按照@Sam的建议将它放在一个Sub中，然后从Button/Worksheet_BeforeDoubleClick调用它。

If you still want to call it from a button then ensure that all relevant checks are in place including a proper error handler.

如果您仍然想从按钮调用它，那么请确保所有相关检查都已就绪，包括一个合适的错误处理程序。