Why has my VBA stopped working only in certain parts?

Question

Recently the VBA has stopped working. Normally it auto-populates today's date into column B when you enter data into column C and also populates today's date into column R when you enter data in column K.

I have recently messed around with protecting sheets and in the end, gave up and I have a feeling it has something to do with it.

It is odd because other areas of the VBA still work.

How can fix this so the VBA still auto-populates the columns it needs to?

        With Target
            If .Count > 1 Then Exit Sub
            If Not Intersect(Range("C:C"), .Cells) Is Nothing Then
                Application.EnableEvents = False
                If IsEmpty(.Value) Then
                    .Offset(0, -1).ClearContents
                Else
                    With .Offset(0, -1)
                        .NumberFormat = "dd mmm yy"
                        .Value = Date
                    End With
                End If
                Application.EnableEvents = True
            End If
        End With
        With Target
            If .Count > 1 Then Exit Sub
            If Not Intersect(Range("K:K"), .Cells) Is Nothing Then
                Application.EnableEvents = False
                If IsEmpty(.Value) Then
                    .Offset(0, 7).ClearContents
                Else
                    With .Offset(0, 7)
                        .NumberFormat = "dd mmm yy"
                        .Value = Date
                    End With
                End If
                Application.EnableEvents = True
            End If
        End With
    
    Const sCell As String = "K2" ' Source First Cell
    Const dCol As Variant = "J" ' Destination Column Id (String or Index)
    
    Dim irg As Range ' Intersect Range
    Dim cOffset As Long ' Column Offset
    With Range(sCell)
        Set irg = Intersect(.Resize(.Worksheet.Rows.Count - .Row + 1), Target)
        If irg Is Nothing Then Exit Sub
        cOffset = Columns(dCol).Column - .Column
    End With
    
    Dim arg As Range ' Current Area of Intersect Range
    Dim cel As Range ' Current Cell in Current Area of Intersect Range
    For Each arg In irg.Areas
        For Each cel In arg.Cells
            If Not IsError(cel.Value) Then
                cel.Offset(, cOffset).Value = cel.Value
            End If
        Next cel
    Next arg
    
End Sub```

Is the posted code the full sub (less the sub header) ? What parts of your code still work? Do any parts of the posted code function still? If you go to the Immediate pane in the VB editor and paste `? Application.EnableEvents` then hit Enter do you get `True` ? — Tim Williams, Apr 06 '21 at 23:21
Apologies, I have now realised that NONE of the code works. Yes the posted code is the full version. I'm not sure what you mean by pasting into the VB editor? — Insider, Apr 06 '21 at 23:29
In the VB editor there is the Immediate pane (Ctrl+G to show it) - you can type `? Application.EnableEvents` and hit Enter and it should give you `True` or `False` https://www.excelcampus.com/vba/vba-immediate-window-excel/ — Tim Williams, Apr 06 '21 at 23:34
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/230823/discussion-between-excel-newman-and-tim-williams). — Insider, Apr 06 '21 at 23:41
In that same Immediate pane, type `Application.EnableEvents = True` and hit enter: that will reset the event handling. In all event handlers where you turn off events, you should have an error handler which makes sure they get turned back on in the event of a run-time error in the code. — Tim Williams, Apr 06 '21 at 23:46

score 1 · Accepted Answer · answered Apr 06 '21 at 23:52

Example using error handler to ensure events aren't left turned off:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim c As Range

    If Target.Cells.CountLarge > 1 Then Exit Sub
    
    If Target.Column = 3 Then
        Set c = Target.Offset(0, -1)
    ElseIf Target.Column = 11 Then
        Set c = Target.Offset(0, 7)
    End If
    
    On Error GoTo haveError 'turn on error handling
    
    If Not c Is Nothing Then 'col 3 or 11...
        Application.EnableEvents = False
        If Len(Target.Value) = 0 Then
            c.ClearContents
        Else
            c.NumberFormat = "dd mmm yy"
            c.Value = Date
        End If
        Application.EnableEvents = True
    End If
    
    Exit Sub 'normal exit here
    
haveError:
    MsgBox "Got an error: " & Err.Description
    Application.EnableEvents = True 'makes sure events are not left off
      
End Sub

Why has my VBA stopped working only in certain parts?

1 Answers1