How do i initiate a macro only when entering on a specific cell or leaving a worksheet without checking cell values

[spurs]

I want to initiate a macro only when an entry is made to the cell M8

In my workbook object I have the following code which runs the macro "Every_Update" each time an entry is made in the worksheet "Sheet1" This works fine.

Worksheets("Sheet1").OnEntry = "Every_Update"

I want to further refine triggering other macros to run

Currently in the Subroutine "Every_Update" I have the following code

Call Analytical_tol

which runs every time an entry is made in "Sheet1"

To safe calculation time, I want to limit "Analytical_tol to run only when and entry is made in cell M8 of Sheet 1

How can i modify the Call Analytical_to line to limit it running only when an entry is made in "M8"


As a 2nd question
In the same workbook i have a chart on a worksheet named "Plot"
At the moment in the "Every Update" subroutine i have a line

Call PlotMaxMC

which controls the recalculation of the data feeding the chart "Plot"

Of course this is happening with every entry on "Sheett1"

This is time consuming and I really only want the subroutine PlotMaxMC to run when i move off of worksheet "Sheet1" or alternatively if i click onto worksheet "Plot"

Of course i want it to run before saving and closing the workbook as well or upon opening

any idea on how to do this?

 

[Micron]

The same event might solve both issues. Consider disabling/removing the calls and moving the called code into the worksheet change event:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("M8")) Is Nothing Then <<if the change is not made in M8, nothing should happen as long as the rest of the code is correct. It's not clear to me if the same sheet is involved or not, so that might be an issue.

 

[spurs]

The same event might solve both issues. Consider disabling/removing the calls and moving the called code into the worksheet change event:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("M8")) Is Nothing Then <<if the change is not made in M8, nothing should happen as long as the rest of the code is correct. It's not clear to me if the same sheet is involved or not, so that might be an issue.

Thank you for your response
As a result of your suggestion, I found this site which clarified things alot

Events In Excel VBA

www.cpearson.com

I created the following test code in the worksheet object but it does not work as expected


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("M8")) Is Nothing Then
Response = MsgBox("M8 Pressed", 2, "M8 pressed")
Else
' Response = MsgBox("M8 Not Pressed", 2, "M8 not pressed")
End If

If Not Intersect(Target, Range("K3:L4,M8")) Is Nothing Then
Response = MsgBox("Range Pressed", 2, "Range pressed")
Else
Response = MsgBox("Range Not Pressed", 2, "Range not pressed")
End If
End Sub


The problem is that i get a Message Box every time i move to a cell or click on a cell even without entry
How do it get it to function only upon entry?

 

[Peter_SSs]

How do it get it to function only upon entry?

By using the event that Micron suggested ..

Private Sub Worksheet_Change(ByVal Target As Range)

.. not the one that you have used.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[spurs]

By using the event that Micron suggested ..


.. not the one that you have used.


BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thank you for your help - I missed that proper selection
I filled out the profile as suggested

Thank you again

 

[Peter_SSs]

You're welcome, glad we could help .. and thanks for updating your profile.