Forum Discussion
Solved
Automatically Fill Columns (Date and Time) with VBA
Hi Community,
I'd like to fix a problem I'm having when trying to run some code in vba that helps me fill a date column and another column with time.
Used code:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Handler
If Target.Column = 2 And Target.Value <> "" Then
Application.EnableEvents = False
Target.Offset(0, 1) = Format(Now(), "dd/mm/yyyy")
Application.EnableEvents = True
End If
Handler:
On Error GoTo Handler2
If Target.Column = 2 And Target.Value <> "" Then
Application.EnableEvents = False
Target.Offset(0, 2) = Format(Now(), "hh:mm AM/PM")
Application.EnableEvents = True
End If
Handler2:
End Sub
The code is executing what I need, but when I change something in the database that this information is being inserted, I come across the error:
naesio_maia , I assume that you are trying to insert a blank row, and that generates the error. If so, then please change the code to as given below. This allows blank row(s) to be inserted :-
Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo Handler If Target.Columns.Count = 1 Then If Target.Column = 2 And Target.Value <> "" Then Application.EnableEvents = False Target.Offset(0, 1) = Format(Now(), "dd/mm/yyyy") Application.EnableEvents = True End If End If Handler: On Error GoTo Handler2 If Target.Columns.Count = 1 Then If Target.Column = 2 And Target.Value <> "" Then Application.EnableEvents = False Target.Offset(0, 2) = Format(Now(), "hh:mm AM/PM") Application.EnableEvents = True End If End If Handler2: End Sub
2 Replies
naesio_maia , I assume that you are trying to insert a blank row, and that generates the error. If so, then please change the code to as given below. This allows blank row(s) to be inserted :-
Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo Handler If Target.Columns.Count = 1 Then If Target.Column = 2 And Target.Value <> "" Then Application.EnableEvents = False Target.Offset(0, 1) = Format(Now(), "dd/mm/yyyy") Application.EnableEvents = True End If End If Handler: On Error GoTo Handler2 If Target.Columns.Count = 1 Then If Target.Column = 2 And Target.Value <> "" Then Application.EnableEvents = False Target.Offset(0, 2) = Format(Now(), "hh:mm AM/PM") Application.EnableEvents = True End If End If Handler2: End Sub- I was wondering if I could add one more condition to have the Target. Column since I understand that the Target. Column is determining which column the information will enter.
