3

Say, I have a procedure which changes the random colour of a cell based on selection change. I know that I can code in a procedure for the worksheet SelectionChange event.

I guess Im asking, as in javascript where we can use addEventListeners, can we do something similar in Excel VBA.

enter image description here

What I am wondering is can this be done dynamically? I.e. when a user chooses an option, then I bind a procedure to the event SelectionChange of the worksheet.

Note, I know that I could declare a global boolean & use that to ascertain user action & use it in my SelectionChange event procedure.

Im just curious whether in Excel VBA, we can dynamically append procedures to events?

pnuts
  • 58,317
  • 11
  • 87
  • 139
Kayote
  • 14,579
  • 25
  • 85
  • 144
  • can you post the whole code? – R.Katnaan Aug 17 '15 at 09:11
  • 1
    Yes you can, you can `Call` or `Run` other procedures (most probably only the public) within your event procedure, so you can do tests in there and call different procedures regarding what the user have inputed! – R3uK Aug 17 '15 at 09:18
  • @R3uK thanks, Im aware of that, Im curious as to whether we can use some sort of event listeners and then append the procedures to the events (like is Javascript etc) – Kayote Aug 17 '15 at 09:22

1 Answers1

2

You can't dynamically assign event handlers. Excel will always call the built-in event handlers (SelectionChange, Calculate, etc) and there's no way to substitute your own. You can create your own Worksheet variable using the WithEvents keyword, however, and choose when to start receiving the events. For example, start with this structure in one of your worksheets:

Dim WithEvents MySheet As Worksheet

Private Sub MySheet_SelectionChange(ByVal Target As Range)

    Debug.Print "Receiving events"

End Sub

As long as MySheet isn't bound to an active sheet, you're not going to receive any events. When you're ready to begin, call a custom subroutine to do the assignment:

Public Sub DoIt()

    Set MySheet = Sheet1

End Sub
Bond
  • 16,071
  • 6
  • 30
  • 53