Which event?

[maurelius]

I want to dynamically update some information in a excel spreadsheet but I cannot seem to find a suitable event to trigger my code.

When I enter data into a cell and then change selection, I want to run some code that picks up the data in the cell I just typed into.

Playing around, I have tried the 'SheetSelectionChanged' event but the 'Target' object refers to the cell I am now on and not the cell I had just typed into.

The 'SheetCalculate' event only triggers when there is a formula being updated to that is no good either.

Can someone point me in the right direction please?

Thanks

 

[CajunCenturion]

Have you tried the Worksheet_Change event?

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[maurelius]

Thanks, but it didn't seem to work.

I have the code in ThisWorkbook module as I need this code to run on every worksheet!!

I cannot believe other people have not wanted to do a similar thing?

 

[CajunCenturion]

==> I have the code in ThisWorkbook module as I need this code to run on every worksheet!!
That may be the problem because it's not a workbook event. It's a worksheet event, and the code needs to be in the worksheet in which it applies.

If you need it to fire for every worksheet, then you'll probably need to insert the code into every worksheet. That being said, if the actual logic inside the event is the same, then you can make that a public subroutine in the workbook module, and simply use a one-line call in each and every worksheet event handler. Then it's a simple copy and paste to all other worksheets.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[joerd]

You can accomplish the same using the Workbook_SheetChange event in the ThisWorbook module.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
MsgBox ("You changed " & Sh.Name & " cell " & Target.Address)
End Sub

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[maurelius]

That seems to do the trick. Thanks

 

[brengine]

Or there's this (see attached).