Unexpected App_Sheet_SelectionChange behavior RRS feed

  • Question

  • I have a custom menu with two items: "On" and "Off". The "On" item calls an Add-In function to init some functionality (crosshair). After pressing the the "On" item, I would like to see the crosshair move on each selection change (i.e a click a cell and a the corresponding row/column gets highlighted). Hence, I have implemented something in Worksheet_SelectionChange() and turning the feature On makes the code in Worksheet_SelectionChange() do something. However, Worksheet_SelectionChange is worksheet-specific and I want to use this functionality across sheets without having to add code to each sheet where I want to use the crosshair. So, I reached for Application events. Hence, I declare

    Public WithEvents App As Application 
    Public Sub Auto_Open()
       Set App = Application
    End Sub  
    Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
       Call DrawCrossHair(...)
    End Sub

    All is cool as long as I don't click out of Excel into another application. Once that happens, App gets reset toNothing and App_SheetSelectionChange does not get called as Auto_Open does not get called on each "Excel_getFocus". How can I prevent this reset of App? Or am I using the wrong tool (App Events) for what I want to achieve?

    Many thanks, Daniel

    Monday, January 21, 2013 7:11 AM

All replies

  • Trapping Application events allows you to trap Workbook and Sheet events in all open workbooks, as you say.

    You will have created a Class for the App events, in turn that Class must be maintained by at least one reference, perhaps declared at the top of a normal module,
    eg Private mAppEvents As Class1.

    If the reference, mAppEvents, is destroyed or falls out of scope the Class will be terminated and you will lose your events. However switching applications would not cause any problems, there must be something else going on.

    If the mAppEvents reference is falling out of scope, you could debug things like this

    Private Sub Class_Terminate()
    MsgBox "Bye"
    ' press F8
    End Sub

    There are some actions that cause the project to recompile, then everything dies. Also don't call simply "End" in code or press the Stop button.

    Peter Thornton

    Monday, January 21, 2013 11:27 AM
  • If this is workbook specific you can use the workbook_sheetselectionchange event - this would mean that you don;t have to code for each sheet (only really a fix if the add-in is for 1 workbook only)

    Rgds Geoff
    Mark as Answer if this resolves your problem or "Vote as Helpful" if you find it helpful.

    Monday, January 21, 2013 1:43 PM