running macro if recalculated

AI Training: For Busy Decision Makers & Professionals Book now

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Running a macro if a recalculated cell changes in a table

Running a macro if a recalculated cell changes in a table

resolvedResolved · Low Priority · Version 2016

Diane has attended:
Excel VBA Introduction course
Excel VBA Intermediate course
Excel VBA Advanced course

Running a macro if a recalculated cell changes in a table

I want to be able to automatically run a macro when a new row is entered on the absence table to record an employee absence. It would only trigger when the calculated value in column G changes. Column G itself is calculated itself from the date difference between column E and F. I am thinking I would use the worksheet calculate (declaration), but I am am struggling with what the syntax would be and how it would re-iterate as the table grows

RE: Running a macro if a recalculated cell changes in a table

Hi Diane,

Thank you for the forum question.

You will need a worksheet event to trigger the macro you want to run.

In the code below I use currentRegion to include new added rows. I start from A1. You may need to change A1 to a cell inside your range.


Private Sub Worksheet_Calculate()

Dim Xrg As Range
Set Xrg = Range("A1").CurrentRegion

If Not Intersect(Target, Xrg) Is Nothing Then
Macro1
End If
End Sub


Please let me know if, it is not what you want.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Running a macro if a recalculated cell changes in a table

Hi Jens

Hope you are safe and well, thank you for the response, it is what I am trying to do but I am getting a compile error when I update the range of 'variable not defined', the variable in question is Target. I can see we haven't defined Target as a variable, but I tried that to no avail.

Kind regards

Diane

RE: Running a macro if a recalculated cell changes in a table

Hi Diane,

Thank you I am fine and I hope that you are fine too.

Sorry I was wrong with my first reply. I forgot that Target was not a declared variable. Try:



Private Sub Worksheet_Calculate()

Dim Xrg As Range
Set Xrg = Range("A1").CurrentRegion.Columns("g")


If Not Intersect(Xrg, Xrg) Is Nothing Then
"the code you want to run"
End If
End Sub


Please let me know if this is working for you.


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

 

Training courses

 

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Adding date and time

Here are two quick ways to add the date and time to your spreadsheet:

1) Type =NOW(), which displays both date and time in the same cell
or
2) Hold Ctrl and type the colon (:) into one cell for the date and the semi-colon(;)into another for the time.

Note that =NOW() updates to the current date/time whenever the spreadsheet recalculates.

View all Excel hints and tips


Server loaded in 0.1 secs.