Worksheet event code from personal.xlsb

Question

Hi all-

I saw this, and thought it was a cool idea to add crosshairs to a sheet upon clicking a cell. I replicated the idea with shapes instead of cell formatting:

https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/18841564-crosshair-display

Then I started thinking about how to put this in my personal.xlsb, and be able to turn it off/on in any workbook, from the ribbon. I started playing with customdocumentproperties as a method to turn it on/off (just have a property value of 1, and the ribbon button triggers a macro to multiply the existing value by -1, and I have a clean flag per workbook; 1= on, -1=off)

However, that still leaves me with a need to trigger the worksheet_change event. Any thoughts on the best way to do so reliably, across any workbook? I thought of using the on/off ribbon button to programmatically add the VBA to each sheet, but that could get messy- there may already be worksheet_selectionchange code there, or the user could add new sheets (without the code)- until they click the button again, there would be nothing to trigger the crosshairs for the new sheet(s).

Just curious how the more advanced developers here would approach it- no need for sample code, I'm using this as a learning experience. Is there a lower-level way to capture/detect the worksheet_change event, outside of the individual worksheet code modules?

Answer 1

Make a new file and add this code into the code module ThisWorkbook:

Option Explicit

Dim WithEvents App As Application

Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
  MsgBox Sh.Name & " " & Target.Address(0, 0)
End Sub

Private Sub Workbook_Open()
  Set App = Application
End Sub

Save, close and reopen. Select a cell and you see that the Msgbox comes up.

Try to open other workbooks, they can also contain a SelectionChange event routine, doesn't matter.

BTW, before you start your project have a look here for the Follow Cell Pointer AddIn:

http://www.jkp-ads.com/download.asp

I guess that is what you want to accomplish.

Andreas,

Answer 2

That's awesome, I haven't used that type of app-level alert before. Thank you!