Get worksheet_Change event to run in any worksheet in any workbook

[Johnny C]

Hi

We have to use an Add-In to access Sun Financials from Excel (Infor Q&A). It's locked so I can't see inside the VBA, but I'm guessing it uses SendKeys as when you use it from a virtual desktop box (Wyse terminal type thing) it randomly turns NumLock off. Which is a big pain. It retrieves data from Sun Financials whenever the worksheet is recalculated.

I've got code to turn Numlock back on if it's turned off, but is there a way to get the code to run after every worksheet change in any change in any workbook, i.e. not using the worksheet_change in the worksheet code for a particular sheet in a particular workbook? Putting the code into every sheet in every workbook in a Finance team folder isn't an option ...

TIA

 

[Fluff]

You can use this for all sheets in a workbook, but not sure how to make it work on any workbook

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

End Sub

 

[Fluff]

Change of plan, from http://www.cpearson.com/excel/Events.aspx
try

Option Explicit

Public WithEvents App As Application

Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)
MsgBox "worked"
End Sub

Private Sub Workbook_Open()
    Set App = Application
End Sub

In a personal macro workbook.

 

[Johnny C]

great, thanks Fluff!

I'll need to change it to Workbook_Activate from Workbook_Open as they have far too many workbooks open at one time

 

[Fluff]

If you put it in a personal macro workbook that loads on startup as an Open event, then it should work on any workbooks they open.

 

[oldbrewer]

Fluff - congrats on promotion - cannot PM you as I am barred.

 

[Fluff]

Fluff - congrats on promotion.

Thanks

 

[Johnny C]

Hmmm, it doesn't work. I tried the code above and then went to Chip's page and tried it using a class module and no joy.

do I need a Reference setting?

 

[Fluff]

Not that I'm aware of. I've got references to
Visual Basic for Applications
Microsoft Excel 15.00 Object Library
Microsoft Forms 2.0 Object Library

 

[Johnny C]

Bizarre. or not. It now works, but it's not worked until everything was correct. Here's what I've got:-

Option Explicit
Public WithEvents App As Application
Private Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Set App = Application
    If CBool(GetKeyState(vbKeyNumlock) And 1) = False Then SendKeys "{NUMLOCK}", True
    'MsgBox "TW ASC worked"
End Sub
Private Sub Workbook_Open()
    Set App = Application
End Sub

Private Sub App_SheetCalculate(ByVal Sh As Object)
    Set App = Application
    'MsgBox "App set on Workbook_SheetCalculate"
End Sub

When I change a cell, it fires the App_SheetCalculate sub then the App_SheetChange sub.
I got it working to fire the subs but then the GetKeyState wouldn't work (it said it was undefined when I opened Excel) so I changed it to a public function (which wouldn't work) and when I set it back to a private declare it worked.

Then it would work the first time I opened a workbook and changed a cell but not after I had swapped to another application such as Outlook.

Now it works fine - for the moment.

One unexpected hurdle I was going to have to fix was restoring the Undo list after resetting the Numlock however ... the Undo list isn't emptied???
Maybe because the code doesn't do anything that impacts the worksheet, no calculations are marked or cells marked as changed, whatever reason I can hit Ctrrl-Z and the Undo works.

I'm not complaining, just never come across it before, I assumed it was a code trigger that emptied the undo list but it must be something in Excel that empties only when a change event occurs from within VBA. As all the code does is a sendKey that event isn't being triggered.

Bizarre but very useful!

The downside I guess is that I'll never be able to turn the NumLock key off. but then, in 26 years of using Excel I've never wanted to. It's a totally useless keyboard key as far as I'm concerned, it's the only key I've never used except to turn NumLock back on when I or the Sun Add-In has turned it off. So I can live with that