Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    New Lounger
    Join Date
    Dec 2014
    Posts
    20
    Thanks
    5
    Thanked 1 Time in 1 Post

    Track changes using worksheet_change event

    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
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Bhushan,

    Welcome to the Lounge as a new poster!

    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    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.

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    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


    Recalc1.png

    Code:
    Private Sub Worksheet_Calculate()
        Application.EnableEvents = False
    '-----------------------------------
    'DECLARE AND SET VARIABLES
        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
    '-----------------------------------
    'SEND COLUMNS TO LOG SHEET
        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
    Attached Files Attached Files

  5. #5
    New Lounger
    Join Date
    Dec 2014
    Posts
    20
    Thanks
    5
    Thanked 1 Time in 1 Post
    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.
    Last edited by bhushanvshah; 2014-12-15 at 05:35. Reason: typo

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    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

  7. #7
    New Lounger
    Join Date
    Dec 2014
    Posts
    20
    Thanks
    5
    Thanked 1 Time in 1 Post
    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
    Last edited by bhushanvshah; 2014-12-15 at 06:19. Reason: typo

  8. #8
    New Lounger
    Join Date
    Dec 2014
    Posts
    20
    Thanks
    5
    Thanked 1 Time in 1 Post
    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
    Attached Files Attached Files

  9. #9
    New Lounger
    Join Date
    Dec 2014
    Posts
    20
    Thanks
    5
    Thanked 1 Time in 1 Post
    Dear MartinM,

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

  10. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    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.

  11. The Following User Says Thank You to Maudibe For This Useful Post:

    bhushanvshah (2014-12-17)

  12. #11
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    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
    Attached Files Attached Files
    Last edited by MartinM; 2014-12-17 at 09:30.

  13. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    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:
    Recalc1.png Recalc2.png

    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
    Attached Files Attached Files

  14. The Following User Says Thank You to Maudibe For This Useful Post:

    bhushanvshah (2014-12-18)

  15. #13
    New Lounger
    Join Date
    Dec 2014
    Posts
    20
    Thanks
    5
    Thanked 1 Time in 1 Post
    Maudibe.... Superb!!!!!!

    Amazing.....!!!!

    thanksssss

  16. #14
    New Lounger
    Join Date
    Dec 2014
    Posts
    20
    Thanks
    5
    Thanked 1 Time in 1 Post
    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.
    Last edited by bhushanvshah; 2014-12-18 at 07:28. Reason: typo

  17. #15
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    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
    Attached Files Attached Files

  18. The Following User Says Thank You to Maudibe For This Useful Post:

    bhushanvshah (2014-12-19)

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •