none
Flagging a row when a cell value is changed (Excel) RRS feed

  • Question

  • Hi Everyone, I'm new to VBA and so far have mostly used it exclusively with Access. But now the door is open and I find myself wanting to do more. This may turn out to have an obviously simple answer, but please bear with my Newbieness. Today I'm playing with a simple spreadsheet in Excel, in which we have some widgets listed and classified for extended warranty pricing purposes. As new widgets get added, or our experience with a particular widget changes we update the clients' master list. More specifically in this case, for any given row that needed to be updated I was updating it's classification values in Column "I" and then tabbing over to Column "N" to change the value from an "N" to a "Y" indicating the class has been changed.  After a few I wondered if this could happen automatically, so I dug around a bit and put this together:

    
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo ErrorHandle
     
    'Use next line to prevent infinite loop if change aimed at same cell (format, color, whatever...)'
    'Application.EnableEvents = False'
    
    'Check if the user made any changes in Col I'
    If Not Intersect(Target, Columns(9)) Is Nothing Then
            
        'Write to relevant cell in Col G'
        Range("N" & Target.Row).Value = "Y"
        
    End If
    
    BacktoNormal:
        Application.EnableEvents = True
        Exit Sub
    
    ErrorHandle:
        MsgBox Err.Description
        Resume BacktoNormal
    End Sub

    I've generalized and annotated it a bit so I can easily apply it elsewhere but it's not working quite as expected in a couple of cases:

    1) If I fill down, using ctrl+D to edit several rows at once it is showing a change to the top row - the cell which I am filling down from. In most cases this has actually had a value in it I am using to fill the other row's but has not been changed itself. However that row is reflecting a change as "Y" in my Column "N" but the rest of the rows are not reflecting the change and still read "N" in Column "N".

    2) Same deal if I instead copy the value from one cell, and then paste it into several - the change is only reflected in the row of the top cell of the group I pasted into.

    Can someone help me figure out how to fix it? Thanks!

    Thursday, October 6, 2016 10:28 PM

Answers

  • For example like this:

    Private Sub Worksheet_Change(ByVal Target As Range)
        On Error GoTo ErrorHandle
    
        'Prevent a cascade of events
        Application.EnableEvents = False
    
        'Check if the user made any changes in Col I
        If Not Intersect(Target, Columns(9)) Is Nothing Then
            'Write to relevant cells in Col N
            Intersect(Target, Columns(9)).Offset(0, 5).Value = "Y"
        End If
    
    BacktoNormal:
        Application.EnableEvents = True
        Exit Sub
    
    ErrorHandle:
        MsgBox Err.Description
        Resume BacktoNormal
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Friday, October 7, 2016 9:17 AM

All replies

  • For example like this:

    Private Sub Worksheet_Change(ByVal Target As Range)
        On Error GoTo ErrorHandle
    
        'Prevent a cascade of events
        Application.EnableEvents = False
    
        'Check if the user made any changes in Col I
        If Not Intersect(Target, Columns(9)) Is Nothing Then
            'Write to relevant cells in Col N
            Intersect(Target, Columns(9)).Offset(0, 5).Value = "Y"
        End If
    
    BacktoNormal:
        Application.EnableEvents = True
        Exit Sub
    
    ErrorHandle:
        MsgBox Err.Description
        Resume BacktoNormal
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Friday, October 7, 2016 9:17 AM
  • Thank you, Hans! That works perfectly.

    An unexpected side effect is I can't seem to use the "undo" command. Is this because of turning events to "False"? Is there a workaround? I don't mind this greatly but other users may. Also, I find that if I mistakenly change a cell and then return it to it's original value (by typing it in right now, since I can't undo) the change indicator reads "Y", as expected, but does Excel keep the original value in memory where I could compare to it therefore returning the "Y" back to "N" if the original value is re-entered?

    Thanks again for the help!

    Friday, October 7, 2016 5:36 PM
  • No, it's not a result of setting EnableEvents to False, but of running the code itself. All VBA code that changes the value of cells disables undo.

    To change "Y" back to "N" if the user restores the original value of a cell would require you to keep track of the previous value of each cell; that would be complicated.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, October 7, 2016 5:59 PM
  • Good to know! Thank you.
    Friday, October 7, 2016 6:51 PM