Results 1 to 19 of 19

Thread: [vba excel] - check if cell has changed.

Hybrid View

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Dec 2007
    Posts
    94

    [vba excel] - check if cell has changed.

    Good evening to everybody.
    I'd like to check the value of a cell with the previous one stored and, if the value has changed, I need to reset two other cells.

    This is my code

    Code:
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    Static precedente
    If Not Intersect(Range("foglio1!b2"), Target) Is Nothing Then
        If Range("foglio1!b2").Value <> precedente Then
            'MsgBox precedente
            'MsgBox Range("foglio1!b2").Value
            Range("foglio1!b5").Value = 0
            Range("foglio1!b6").Value = 0
            precedente = Range("foglio1!b2").Value
    End If
    End If
    End Sub
    It seems to work when the file is already opened. However if I close it and reopen it, even though I rewrite in the cell the same value, the other cells are set to 0. I add the msgboxes and I've understood that the problem is that the precedente variable has no value. How can I store it to make comparison as soon as I open the file? Thanks in advance.

  2. #2

    Thread Starter
    Lively Member
    Join Date
    Dec 2007
    Posts
    94

    Re: [vba excel] - check if cell has changed.

    Maybe I found a solution.

    This is my new code

    Code:
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    Application.EnableEvents = False
    If Not Intersect(Range("foglio1!b2"), Target) Is Nothing Then
        nuovo = Range("foglio1!b2").Value
        Application.Undo
        precedente = Range("foglio1!b2").Value
            If nuovo <> precedente Then
                Range("foglio1!b5").Value = 0
                Range("foglio1!b6").Value = 0
                Range("foglio1!b2").Value = nuovo
            End If
    End If
    Application.EnableEvents = True
    End Sub
    What do you think about it?
    Unluckily have another problem. After I changed value of b2 cell, this cell stays select. I'd like that if I press enter it were selected b3 cell, if I press right arrow were selected c2 cell and so on.

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: [vba excel] - check if cell has changed.

    as the static will have no value carried forward from the last time the file was open you need to save the value somewhere then
    vb Code:
    1. if precedente = "" then precedente = 'previous value
    you could store in customdocumentproperties or elsewhere
    alternatively you could use a global variable in stead of static the assign the stored value to it in the workbook open event
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Dec 2007
    Posts
    94

    Re: [vba excel] - check if cell has changed.

    Hi westconn1 and thanks for your reply. I don't know anything about customdocumentproperties. I'll search on google about it.
    However the second code I posted seems to work well. Can you give some advice about cursor movements detecting. I thought that application.onkey method was what I was looking for but maybe I'm not able to use it in the right way.

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: [vba excel] - check if cell has changed.

    However the second code I posted seems to work well.
    as your code changes a cell value within the sheetchange event it will again fire the sheetchange event and is fraught with problems, you would need to set a boolean variable, to prevent the change event from firing from changing the code, as it is likely to be recursive and run forever (or until it crashes)
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  6. #6
    Head Hunted anhn's Avatar
    Join Date
    Aug 2007
    Location
    Australia
    Posts
    3,669

    Re: [vba excel] - check if cell has changed.

    Quote Originally Posted by westconn1 View Post
    as your code changes a cell value within the sheetchange event it will again fire the sheetchange event and is fraught with problems, you would need to set a boolean variable, to prevent the change event from firing from changing the code, as it is likely to be recursive and run forever (or until it crashes)
    Application.EnableEvents = False
    is a good use. After that, all events are disabled so the Change event will not fire itself again.

    I found that without switching off events, the Change event will fire itself 207 times then stop (not sure why 207).

    The code in post#2 seems to works as what required if only one cell B2 is modified.
    But "Application.Undo" will cause a side effect if user modifies many cells at the same time such as copy-paste to multiple cells that includes B2 then all modified cells will be undone, not just B2.

    The solution pete mentioned in post#3 is better:
    alternatively you could use a global variable in stead of static the assign the stored value to it in the workbook open event
    In "foglio1" module:
    Code:
    Option Explicit
    
    Public precedent '-- the keyword Global does not works.
    
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Target, Me.[B2]) Is Nothing Then
            If Me.precedent <> Me.[B2].Value Then
                Me.[B5] = 0
                Me.[B6] = 0
                Me.precedent = Me.[B2].Value
            End If
        End If
    End Sub
    In ThisWorkbook module:
    Code:
    Option Explicit
    
    Private Sub Workbook_Open()
        With Worksheets("foglio1")
            .precedent = .[B2]
        End With
    End Sub
    • Don't forget to use [CODE]your code here[/CODE] when posting code
    • If your question was answered please use Thread Tools to mark your thread [RESOLVED]
    • Don't forget to RATE helpful posts

    • Baby Steps a guided tour
    • IsDigits() and IsNumber() functions • Wichmann-Hill Random() function • >> and << functions for VB • CopyFileByChunk

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Dec 2007
    Posts
    94

    Re: [vba excel] - check if cell has changed.

    Thank you very much anhn. You're always very kind.
    Now it's perfect. Have a nice sunday.

  8. #8
    New Member
    Join Date
    Jan 2012
    Posts
    3

    Re: [vba excel] - check if cell has changed.

    Hi,

    Thank you for the brilliant notes above. I was trying something similar and worked like a charm for me.
    Although, I want to do something like this:
    <CODE>
    Option Explicit


    Public p2
    Public p3
    Public p4


    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.[F2:F4]) Is Nothing Then
    If Not Me.[F2] = ""
    If Me.p2 <> Me.[F2].Value Then
    Me.[G2] = Date
    Me.p2 = Me.[F2]
    End If
    Else
    Me.[G2] = ""
    End If
    If Not Me.[F3] = ""
    If Me.p3 <> Me.[F3].Value Then
    Me.[G3] = Now()
    Me.p3 = Me.[F3]
    End If
    Else
    Me.[G3] = ""
    End If
    If Not Me.[F4] = ""
    If Me.p4 <> Me.[F4].Value Then
    Me.[G4] = Date
    Me.p4 = Me.[F4]
    End If
    Else
    Me.[G4] = ""
    End If
    </Code>

    I'm a complete noob to VB and thus I have no idea how looping works in a case like this. Any help would be greatly appreciated.

  9. #9
    New Member
    Join Date
    Jan 2012
    Posts
    3

    Re: [vba excel] - check if cell has changed.

    Hi,

    Thank you for the brilliant notes above. I was trying something similar and worked like a charm for me.
    Although, I want to do something like this:
    Code:
    Option Explicit
    
    
    Public p2
    Public p3
    Public p4
    
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.[F2:F4]) Is Nothing Then
    If Not Me.[F2] = ""
    If Me.p2 <> Me.[F2].Value Then
    Me.[G2] = Date
    Me.p2 = Me.[F2]
    End If
    Else
    Me.[G2] = ""
    End If
    If Not Me.[F3] = ""
    If Me.p3 <> Me.[F3].Value Then
    Me.[G3] = Now()
    Me.p3 = Me.[F3]
    End If
    Else
    Me.[G3] = ""
    End If
    If Not Me.[F4] = ""
    If Me.p4 <> Me.[F4].Value Then
    Me.[G4] = Date
    Me.p4 = Me.[F4]
    End If
    Else
    Me.[G4] = ""
    End If
    I'm a complete noob to VB and thus I have no idea how looping works in a case like this. Any help would be greatly appreciated.

  10. #10
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: [vba excel] - check if cell has changed.

    I'm a complete noob to VB and thus I have no idea how looping works in a case like this. Any help would be greatly appreciated.
    help to do what? you do not specify what result you wish to achieve, or what loop you want to do, this would appear to only fire when cell f2 to f4 are edited, what is the problem with the current code?

    you should use indenting in your code so that you can easily identify what starts and finishes where
    vb Code:
    1. Private Sub Worksheet_Change(ByVal Target As Range)
    2.     If Not Intersect(Target, Me.[F2:F4]) Is Nothing Then
    3.         If Not Me.[F2] = ""
    4.             If Me.p2 <> Me.[F2].Value Then
    5.                 Me.[G2] = Date
    6.                 Me.p2 = Me.[F2]
    7.             End If
    8.             Else
    9.             Me.[G2] = ""
    10.         End If
    11.         If Not Me.[F3] = ""
    12.             If Me.p3 <> Me.[F3].Value Then
    13.                 Me.[G3] = Now()
    14.                 Me.p3 = Me.[F3]
    15.             End If
    16.             Else
    17.             Me.[G3] = ""
    18.         End If
    19.         If Not Me.[F4] = ""
    20.             If Me.p4 <> Me.[F4].Value Then
    21.                 Me.[G4] = Date
    22.                 Me.p4 = Me.[F4]
    23.             End If
    24.             Else
    25.             Me.[G4] = ""
    26.         End If
    27. End Sub
    i can now see you are missing 3 "then" and one "end if"
    the compiler should have given you messages about those anyway
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  11. #11
    New Member
    Join Date
    Jan 2012
    Posts
    3

    Re: [vba excel] - check if cell has changed.

    Firstly, Many thanks for your prompt response.
    Secondly, apologies..I wasnt clear enough.
    About the code being indented, it was when I pasted it..weird it didnt come through.
    As you can see I'm writing the same piece of code for F2:F4. I want to do this for the entire F range or at least 100 rows. As in, if any value in F range changes, I want to trigger the macro to insert date in the corresponding G cell. For eg. If my F5 says "New" when I open the file and I change it to say "Closed", G5 should have today's date. So on and so forth.
    I could write a function to do this and then call it but the problem remains for the no. of global variables. Say, I wanna do it for 100 rows, do I have to create 100 global variables to capture the state of column F? At present, I'm creating 30 global variables and calling the method 30 different times with 30 different cells. So, it works fine for 30 rows.

    The code is not actual code..just a representation.

    Code:
    Option Explicit
    
    Public p2, p3, p4 ... p30
    
    Function RepeatIt(ByRef F As Range, ByRef G As Range, ByRef Target As Range, ByVal p As String)
            If Not F = "" Then
                  If p <> F.Value Then
                        G = Date
                        p = F
                 End If
            Else
                  G = ""
            End If
    End Function
    
    Private Sub Worksheet_Change(ByVal Target As Range)
            If Not Intersect(Target, Me.[F2:F30]) Is Nothing Then
                   Call RepeatIt(Me.[F2], Me.[G2], Target, Me.p2)
                   Call RepeatIt(Me.[F3], Me.[G3], Target, Me.p3)
                                          ....
                   Call RepeatIt(Me.[F30], Me.[G30], Target, Me.p30)
    I'm sure there is an easier way to do this as its a pain to create so many variables and call the same function so many times.
    Many Many Thanks.

  12. #12
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: [vba excel] - check if cell has changed.

    how about like?
    vb Code:
    1. if target.row = 6 then
    2.   select case target
    3.     case "Closed": target.offset(, 1) = date
    4.     case "new": target.offset(, 1) = 123
    5.     ' other cases
    6.   end select
    7. end if
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  13. #13
    New Member
    Join Date
    Jan 2012
    Posts
    4

    Re: [vba excel] - check if cell has changed.

    I'm sorry guys, but I recently came across this thread, and I seem to be having a probelm with a simple code. I am trying to set up my sheet to reset cell values (k4,k5,k6,k9) when the target cell has been changed (d3). Can someone please help me, I would grately appreciate it.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Not Intersect(Range("$D$3"), Target) Is Nothing Then
    nuovo = Range("$D$3").Value
    Application.Undo
    precedente = Range("$D$3").Value
    If nuovo <> precedente Then
    Range("$K$4").Value = 0
    Range("$K$5").Value = 0
    Range("$K$6").Value = 0
    Range("$K$9").Value = 0
    Range("$D$3").Value = nuovo
    End If

    End If
    Application.EnableEvents = True
    End Sub

  14. #14
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: [vba excel] - check if cell has changed.

    what is the problem? the code looks ok and appears to work
    are you getting errors? wrong results? nothing happening?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  15. #15
    New Member
    Join Date
    Jan 2012
    Posts
    4

    Re: [vba excel] - check if cell has changed.

    Nothing happens. It doesn't kick back an error, warning, change cell values or nothing. I am lost as to what could be the problem. Did it work when you plugged it in?

  16. #16
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: [vba excel] - check if cell has changed.

    Did it work when you plugged it in?
    put some 0 where expected

    are you sure the procedure was in the correct place, must be in the code pane for the worksheet containing the cells
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  17. #17
    New Member
    Join Date
    Jan 2012
    Posts
    4

    Re: [vba excel] - check if cell has changed.

    Yes, the code is enter for sheet 3, which is where the cells are located. What is worst is that two days ago, I had the code working correctly, and didn't save it. I continued to make changes, and now, nothing happens. Is there a feature that I could have disabled?

  18. #18
    New Member
    Join Date
    Jan 2012
    Posts
    4

    Re: [vba excel] - check if cell has changed.

    Anyway input on what I can change in the following formula:

    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    Application.EnableEvents = False
    If Not Intersect(Range("Sheet1!$B$1"), Target) Is Nothing Then
    nuovo = Range("Sheet1!$B$1").Value
    Application.Undo
    precedente = Range("Sheet1!$B$1").Value
    If nuovo <> precedente Then
    Range("Sheet3!$K$4").Value = 0
    Range("Sheet3!$K$5").Value = 0
    Range("Sheet3!$K$6").Value = 0
    Range("Sheet3!$K$9").Value = 0
    Range("Sheet1!$B$1").Value = nuovo
    End If

    End If
    Application.EnableEvents = True
    End Sub



    This formula is located the "ThisWorkBook" section. Sorry for the bother.
    Last edited by TanktheFrank; Feb 1st, 2012 at 11:24 AM.

  19. #19
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: [vba excel] - check if cell has changed.

    i tested the code initially and it worked so i do not know what your problem is
    you should not need to call undo as there is no change unless it is not equal to pevious
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

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



Click Here to Expand Forum to Full Width