Excel VBA: update cell based on previous cells change

Question

I'm working on an Excel worksheet and using VBA to complete and update information on the cells.

There are seven columns in the Excel table. Three of them are drop-down lists with Data Validation, which I used the following VBA code to fill them.

Private Sub TempCombo_KeyDown(ByVal _KeyCode As MSForms.ReturnInteger, _ ByVal Shift As Integer)

    'Ocultar caixa de combinação e mover a próxima célula com Enter e Tab

    Select Case KeyCode
        Case 9
            ActiveCell.Offset(0, 1).Activate
        Case 13
            ActiveCell.Offset(1, 0).Activate
        Case Else
            'Nada
    End Select

End Sub

These columns also work with autocomplete, using the code below:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim str As String
    Dim cboTemp As OLEObject
    Dim ws As Worksheet
    Dim wsList As Worksheet

    Set ws = ActiveSheet
    Set wsList = Sheets(Me.Name)

    Application.EnableEvents = False
    Application.ScreenUpdating = False

    If Application.CutCopyMode Then
        'Permite copiar e colar na planilha
        GoTo errHandler
    End If

    Set cboTemp = ws.OLEObjects("TempCombo")

    On Error Resume Next

    With cboTemp
        .Top = 10
        .Left = 10
        .Width = 0
        .ListFillRange = ""
        .LinkedCell = ""
        .Visible = False
        .Value = ""
  End With

  On Error GoTo errHandler

      If Target.Validation.Type = 3 Then
          Application.EnableEvents = False
          str = Target.Validation.Formula1
          str = Right(str, Len(str) - 1)
          With cboTemp
              .Visible = True
              .Left = Target.Left
              .Top = Target.Top
              .Width = Target.Width + 15
              .Height = Target.Height + 5
              .ListFillRange = str
              .LinkedCell = Target.Address
         End With
         cboTemp.Activate
         'Abrir a lista suspensa automaticamente
         Me.TempCombo.DropDown
    End If

    errHandler:
        Application.ScreenUpdating = True
        Application.EnableEvents = True
    Exit Sub

End Sub

Anytime I update any cell on a row, I want that the content of the seventh column of this row is updated with the current date.

I tried using the following code, but it only works with common cells, the ones that I manually type its content. I want the seventh column to be updated when I change the drop-down list selection also.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Row = 1 Then Exit Sub
        Application.EnableEvents = False
        Cells(Target.Row, "U").Value = Date

End Sub

Is there any way to update the content of the column as I said before? Even when I change the option selected in the drop-down list?

In your first sub, why are you Activating the cells? Do you want to do something with those? Or are you doing that, in order to trigger the Worksheet_SelectionChange? — BruceWayne
@BruceWayne to trigger the Worksheet_SelectionChange, yes. — cfrancklin
If you are using Data Validation, List to generate your drop-downs then they should be triggered by the Worksheet_Change. No other code is necessary and possibly confusing the issue. Remember to turn EnableEvents back on before exiting the Worksheet_Change event sub. — user4039065
Seems we were typing at the same time, Jeeped. I agree that EnableEvents not being turned back on is the issue. — J. Garth

J. Garth J. Garth · Accepted Answer · 2017-03-08T19:07:03

Your code is fine except that you need to turn events back on. You have stopped events from firing with this line: Application.EnableEvents = False but you never turn the event firings back on again. So your code will work the first time you change a cell, the Worksheet_Change event will fire as expected. However, within this sub you have set EnableEvents to false and then never set it back to true. So you have stopped all future events, including this one, from firing again in the future. Here is the solution:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Row = 1 Then Exit Sub
        Application.EnableEvents = False
        Cells(Target.Row, "U").Value = Date
        Application.EnableEvents = True

End Sub

Excel VBA: update cell based on previous cells change

1 Answers