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?

Why don't you just define a public sub or function that does your actions, then you can call it whenever you want, (including from inside your BeforeDoubleClickEvent). — Sam
– Sam, Commented Nov 28, 2013 at 19:42
Thanks to you both. Sam, I'm trying your suggestion, but moving all the code from the Event module to a standard module seems complicated. I have a lot of controls to refer to, and I can't just say "Me.CancelButton" anymore. Do you know where I can read about this? — Shawn V. Wilson
– Shawn V. Wilson, Commented Nov 28, 2013 at 22:03

Siddharth Rout · Accepted Answer · 2013-11-28 20:03:14Z

5

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

Insert a button
Insert a blank chart
Insert an image in the chart. Let the image be highlighted

enter image description here

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.

enter image description here

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.

edited Nov 28, 2013 at 20:03

answered Nov 28, 2013 at 19:57

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2140173 Over a year ago

great analysis and explanation

Collectives™ on Stack Overflow

Excel VBA: How to trigger an Event from code?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related