Track changes using worksheet_change event

Topic

New Reply

Hi..

Please guide me..

Column B of my sheet contains price which is got from another workbook(which in turn gets it from a dde connection).

[ Data is pulled in sheet using the formula =[Input.xlsm]Sheet1!B8 ]

Column F & G contains formulas, and if the condition is met, we get a result there.
I wish to maintain a log to record the column F & Column G events.

thanks,
Bhushan

Reply | Quote

Replies

Bhushan,

Welcome to the Lounge as a new poster! :cheers:

I don’t know of an easy way to do this.

Normally, when you want to track changes to some cells you would use the Worksheet_Change event. However, recalculation of formulas does NOT trigger this event!

The other event Worksheet_Calculate fires AFTER the sheet has been recalculated.

The only way I can see to do what you want would be to make a Value copy of the sheet to another sheet then trap the Recalculate event and do a cell by cell comparison of the current values and the saved values, record the results, then do a new value copy to be used the next time a recalc is performed.

Hopefully, someone else here has a better idea.

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

I hunted high and low to find a simple solution to this and concluded that the technique which RG suggests is the least difficult. My variation is as below . . .

I use a macro to import the data and then cycle through the values, storing each previous value, updating it and noting when there has been a change – then taking a number of actions dependent on that.

It wasn’t much effort and has turned out to be quite a powerful tool – I can note the change in the value, the date and time it occurred and I construct a short, separate list of changes for easy reference.

Reply | Quote

Brushan,

Consider the following workbook that calculates the perimeter and area of a circle based on radius values on sheet 2. On sheet 1, columns F and G have the formulas. If you change the radius value on Sheet 2 Cell A1, all the Perimeters and Areas will recalculate on sheet 1. The Worksheet_Calculate event subroutine will write the columns to a log sheet with a Date/Time stamp. No cell comparison is needed. I have placed the starting values on the Log sheet to start.

HTH,
Maud

38697-Recalc1

Code:

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
[COLOR=”#008000″]’———————————–
‘DECLARE AND SET VARIABLES[/COLOR]
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim ws3 As Worksheet
    Set ws1 = Worksheets(“Sheet1”)
    Set ws2 = Worksheets(“Sheet2”)
    Set ws3 = Worksheets(“Log”)
    EndRow = ws1.Cells(Rows.Count, 6).End(xlUp).Row
    NextCol = ws3.Cells(2, Application.Columns.Count).End(xlToLeft).Column + 1
[COLOR=”#008000″]’———————————–
‘SEND COLUMNS TO LOG SHEET[/COLOR]
    ws3.Cells(1, NextCol) = Now
    ws3.Cells(2, NextCol) = “Perimeter”
    ws3.Cells(2, NextCol + 1) = “Area”
    For I = 2 To EndRow
        ws3.Cells(I + 1, NextCol) = ws1.Cells(I, 6)
        ws3.Cells(I + 1, NextCol + 1) = ws1.Cells(I, 7)
    Next I
    Application.EnableEvents = True
End Sub

Reply | Quote

Thank you very much Retired Geek, MartinM and Maudibe..

Maudibe…
I am getting an error.. on the line:
Set ws3 = Worksheets(“Log”)

when i try to get an input for radius from another open worksheet where the actual changes take place.
(i.e) =[Book1]Sheet1!$A$1

Dear MartinM and Retired Geek,
Since I am not very familiar with this complicated method. I request you to kindly guide me with a basic excel sheet, from which i will suitably move on.

regards
Bhushan.

Reply | Quote

bhushanvshah,

This was set up as an example to use the workbook and worksheets that I provided.

Re-download the workbook. Don’t change the name of any of the worksheets. I can alter the code if you want to pursue this so you can rename them anything you want. You should have 3 sheets and the one in question should be Log. Go to sheet 2 within the same workbook. Change the value in A2. The perimeters and areas will change on sheet1 and the log will be updated. I can change the code to adapt your formulas and workbooks.

In other words, you cannot copy the code from my workbook and place it in yours without adapting it. Let me know if it works in my sample

Maud

Reply | Quote

Maudibe,

Thanks for reply.
What i meant is, I dont enter the value of the radius in sheet2 of the file Recalc.xlsm

But instead grab the value in sheet2 of Recalc.xlsm from another open workbook ( Lets name that workbook as Input.xlsm)

Step one: Data in entered in Input.xlsm,
step two: Values is pulled into sheet2 of Recalc.xlsm using formula =[Input.xlsm]Sheet1!$A$1
Step three: Calcultions take place in sheet1 of Recalc.xlsm
step four: When a certain criteria is met in a row of Sheet1, a cell will generate a value in that row “Target Achieved”.
Step five: The row containing “Target Achieved” is copied to the log of Recalc.xlsm

thanks
bhushan

Reply | Quote

Hi Maudibe..

I have attached your file with reqd changes to explain more in detail.
I have removed all code, so that it does not interfere in my explanation of the problem.

thanks.
bhushan

Reply | Quote

Dear MartinM,

Could you write a small example for me, from which i will take off.
thanks
Bhushan

Reply | Quote

Hi Bhushan

I have created a very simple example which you can adapt to your specific needs. Note: it is created in Excel 2003.

It has a set of existing Prices, in yellow in column C. When you click on the “Update Prices” button it fetches new Prices – which in this simple example are in blue in column J – and updates the existing prices. It also:

* records what change there has been, if any
* makes a note of the date and time of the last change

I hope this helps to get you going.

Martin

Reply | Quote

I can change the code to adapt your formulas and workbooks

Bhushanvshah,

I got all that on your opening post. The point I was making is that a recalculation can initiate data sent to a log. Whether new values come from sheet 2, another workbook, or the dark side of the moon, is totally irrelevant. Without a sample spreadsheet with formulas, as you have just provided, there was no way I could make it specific to your needs. Now that you have provided a sample, I can. I will take a look at it.

Reply | Quote

bhushanvshah,

If Martin’s clever work does not meet your needs, consider the following workbook using your file in post #8.

The values changed on the Input sheet (sheet2) equates to values being changed in another workbook (Input.xlsm). When that value is changed, it changes a corresponding value in column B of sheet 1. The perimeter in column F of the same row is automatically calculated and the value updated. Based on this new value, the conditional formulas in column H and I of the same row are also updated. If the value in column H is “Target Achieved” then the adjacent value in Column I of the same row gets written to the log and appended in the next row and time stamped. If the target has not been achieved, then no value gets written to the log. I have protected sheet1 (with no password) to force you to enter values on the Input sheet simulating values being entered from another workbook.

Value of 5 assigned on Input sheet:
38736-Recalc1 38737-Recalc2

How it works:
To make this happen, I used the Application.Caller to capture the value of the cell that was just changed. This has to be done inside a User Defined Function (UDF). I created a UDF for the values in column H called Status. The syntax is =Status(perimeter) where perimeter in the corresponding perimeter in column F. In H2, enter the formula =Status(F2) then copy down. The UDF will return either “Target Achieved” or “” based on the value in F. Application.Caller, inside the UDF, also assigns the returned value to a public variable (StatusVal) which can be then used outside the function, primarily in the Worksheet_Calculation event of sheet 1.

Status UDF in a standard module:

Code:

Public StatusVal As Range

Function Status(perimeter As Double) As String
    Set StatusVal = Application.Caller
    If perimeter > 10 Then
        Status = “Target Achieved”
    Else:
        Status = “”
    End If
    End Function

So either “Target Achieved” or “” is returned to the cell in Column H and StatusVal knows this value. Worksheet_Calculation is then triggered and if StatusVal = “Target Achieved” then Column I is written to the log. (See above image)

In Sheet1 Worksheet Module:

Code:

Private Sub Worksheet_Calculate()
‘———————————–
‘DECLARE AND SET VARIABLES
    Dim ws1 As Worksheet
    Dim ws3 As Worksheet
    Set ws1 = Sheet1
    Set ws3 = Sheet3
    NextRow = ws3.Cells(Rows.Count, 1).End(xlUp).Row + 1
‘———————————–
‘TEST IF TARGET WAS ACHIEVED
If StatusVal = “Target Achieved” Then
    ws3.Cells(NextRow, 1) = StatusVal.Offset(0, 1)
    ws3.Cells(NextRow, 2) = Now
End If
End Sub

Reply | Quote

Maudibe…. Superb!!!!!!

Amazing…..!!!!

thanksssss

Reply | Quote

Maudibe… One final request…

In the example we are using a common target value of 10 to test the condition for all the rows.

so in Column D of sheet1, I give the heading as TgtValue
and under that column i give the respective values, say 15,22,28,30,35

Finally, if Perimeter>=TgtValue, then the status column will give “Target Achieved” and then as usual the log is created.

thanks
bhushan.

Reply | Quote

bhushanvshah,

Real easy adjustment! Besides adding the new column on the sheet1, there only needs to a change to 1 line of code:

From:

Code:

If perimeter > 10 Then

To:

Code:

If perimeter > StatusVal.Offset(0, -4).Value Then

Which is saying instead of: “If the perimeter is greater than 10 then”

It is saying: “If the parameter is greater than the value 4 columns to the left of the cell that called the formula then”

NOTE: Keep in mind that you had Column G hidden so I left it that way (the reason for the -4 instead of -3).

Code:

Public StatusVal As Range

Function Status(perimeter As Double) As String
    Set StatusVal = Application.Caller
    If perimeter > StatusVal.Offset(0, -4).Value Then
        Status = “Target Achieved”
    Else:
        Status = “”
    End If
    End Function

HTH,
Maud

Reply | Quote

Maud,

Interesting code!

It got me thinking (dangerous) about how this could be generalized to some extent to be used like the “Target” Parameter in the Worksheet_Change event in any old function. Here’s what I came up with:

Code:

Option Explicit

Public Function zCallerAdrTest(Optional Target As Variant) As String

'+-----------------------------------------------------------------+
'| Calling Examples:                                               |
'|                                                                 |
'| D2: =Test()                                                     |
'|    Will return $D$2 & $D$4 Respectively                         |
'|                                                                 |
'| C4: =Test($F$1)                                                 |
'|    Will return $F$1 & $F$3 Respectively                         |
'|                                                                 |
'+-----------------------------------------------------------------+

  Application.Volatile
  
  If IsMissing(Target) Then Set Target = Application.Caller
  
'***  For testing purposed only!  ****
'  MsgBox "I was called by: " & Target.Address() & vbCrLf & _
'         "Offset 2 Rows  : " & Target.Offset(2, 0).Address(), _
'         vbOKOnly + vbInformation, "Testing Parameter Default Value"
         
'*** Your function code here!  Make sure to change the return type ***
'*** to the datatype the function should return,                   ***
'*** e.g. Integer, Double, etc.  (change function name too)        ***

zCallerAdrTest = "Your results here in appropriate format"
        
End Function   'zCallerAdrTest()

'*****      Practical Example      *********

Public Function dCalcRow(Optional Target As Variant) As Double

'+-----------------------------------------------------------------+
'| Calling Examples:                                               |
'|                                                                 |
'| D4: =100  D5: = 65                                              |
'|                                                                 |
'| A4: =dCalcRow()     Will return 35                              |
'|                                                                 |
'| A5: =dCalcRow(A4)   Will return 35                              |
'|                                                                 |
'+-----------------------------------------------------------------+

  Application.Volatile
  
  If IsMissing(Target) Then Set Target = Application.Caller

  dCalcRow = Target.Offset(0, 3).Value - Target.Offset(1, 3).Value
        
End Function   'dCalcRow

38752-MaudCaller

Coding in this fashion allows you to use the same code to call a function using the default (location of the function) or a specified location as the starting point for all your calculations. Of course if you have a lot of cells to reference you can use:

Code:

With Target
     x = .offset(.....
    ...
End With

I hope some of you may find this technique useful. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Superb once again Maudibe… its real fantastic…

Reply | Quote

Thanks bhushan.

RG,

Your code shows another example of the same technique I used in post #15 of offsetting from a reference point established by the Application.Caller.

Reply | Quote

Thanks bhushan.

RG,

Your code reinforces the same technique I used in post #15 of offsetting from a reference point established by the Application.Caller. Thanks.

Code:

[COLOR=”#0000FF”]Public StatusVal As Range[/COLOR]

Function Status(perimeter As Double) As String
    [COLOR=”#0000FF”]Set StatusVal = Application.Caller
    If perimeter > StatusVal.Offset(0, -4).Value Then
[/COLOR]        Status = “Target Achieved”
    Else:
        Status = “”
    End If
    End Function

However, the real versatility lies in assigning it to a public range variable (above code) so it can be utilized outside the function anywhere in the Application.

Post #12: Used in the Worksheet_Calculate event subroutine

Code:

Private Sub Worksheet_Calculate()
[COLOR=”#008000″]’———————————–
‘DECLARE AND SET VARIABLES[/COLOR]
    Dim ws1 As Worksheet
    Dim ws3 As Worksheet
    Set ws1 = Sheet1
    Set ws3 = Sheet3
    NextRow = ws3.Cells(Rows.Count, 1).End(xlUp).Row + 1
[COLOR=”#008000″]’———————————–
‘TEST IF TARGET WAS ACHIEVED[/COLOR]
[COLOR=”#0000FF”]If StatusVal = “Target Achieved” Then
    ws3.Cells(NextRow, 1) = StatusVal.Offset(0, 1)
[/COLOR]    ws3.Cells(NextRow, 2) = Now
End If
End Sub

This opens doors to establish target ranges for events and routines that would normally have no way of reliably obtaining the calling address. Returning the Activecell.Address is inconsistent at best depending how the user exits the cell with the function (Enter and tab, mouse clicks another cell), as well as returning the addresses of the precedent cells. Using the following test function in a standard module, enter the formula =test() in D1. In E1, toggle through different numeric values and you will notice the address of the precedent returned, not the address of the cell that has the function. This exemplifies how useful this technique above of grabbing the address of the calling cell can be while retuning the Activecell is not.

Code:

Public Function test() As Variant
Application.Volatile
test = Range(“E1”)
MsgBox ActiveCell.Address
End Function

Reply | Quote

Maud,

Good points all! Thanks! :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote