Excel VBA: How to trigger an Event from code?

Question

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.

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?

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

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