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```
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