VBA for Auto Date/Time

bill351

Board Regular
Joined
Jan 19, 2009
Messages
83
Hey Guys n Girls
I was given a VBA solution by Joe4 (thanks heaps Joe) and it does just what it is suppose to do very well. So when I enter a name in column A I get the current date and time of that entry and what Joe fixed for me is when I do a later entry that current date and time is correct and doesn't change any of the previous entries.
However if (in my testing) if I remove a name in Column A I get a Run Time error '13': Type Mismatch. Below is the VBA with the with the debug highlight

Private Sub Worksheet_Change(ByVal Target As Range)


Dim myFirstDataRow As Long

' Specify where the first data row starts
myFirstDataRow = 2

' Check for updates in column A
If (Target.Column = 1) And (Target.Row >= myFirstDataRow) And (Target <> "") Then
' Capture date stamp in column C
Target.Offset(0, 2) = Date
' Capture time stamp in column D
Target.Offset(0, 3) = Time
End If


End Sub

So my obvious question is how would I fix this?
please excuse my ignorance I really struggle with VBA

Cheers
Bill from OZ
 
Last edited:

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Can you please provide a sample of the data your using? I just tested the code you're using and was unable to replicate the issue your experiencing.
 
Upvote 0
Hey mrmmickle1
This what I am trying to make work

1Hrs to dateDate
Auto
Time Called
Auto
Name
John10/06/20163:05 PM
Greg10/06/20163:06 PM
10/06/20163:06 PM
Toby

<tbody>
</tbody>

If I remove a name Actually if I highlight and remove all of line 4 (john) name, date and time the Debug message appears. I would expect If I removed a name the date and time would go as well but it stays and the debug message appears
I hope this is a bit clearer
Cheers Bill
 
Upvote 0
Hey mrmmickle1
This what I am trying to make work

1Hrs to dateDate
Auto
Time Called
Auto
Name
John10/06/20163:05 PM
Greg10/06/20163:06 PM
10/06/20163:06 PM
Toby

<tbody>
</tbody>

If I remove a name Actually if I highlight and remove all of line 4 (john) name, date and time the Debug message appears. I would expect If I removed a name the date and time would go as well but it stays and the debug message appears
I hope this is a bit clearer
Cheers Bill
Hi Bill,

The error is occurring as when you delete a row you are in effect updating multiple cells at once and the Worksheet_Change event isn't liking it. If this only happens when you are trying to clear the contents of an entire row then you can add and additional line of code to handle this as follows:

Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)


Dim myFirstDataRow As Long

If Target.Cells.Count > 1 Then Exit Sub


' Specify where the first data row starts
myFirstDataRow = 2


' Check for updates in column A
If (Target.Column = 1) And (Target.Row >= myFirstDataRow) And (Target <> "") Then
' Capture date stamp in column C
Target.Offset(0, 2) = Date
' Capture time stamp in column D
Target.Offset(0, 3) = Time
End If


End Sub
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top