VBA Timestamp & Update Stamp

[bhalbach]

Can someone give me some insight into making a Time Stamp in column Q of an Entry into my sheet and then if I make a change to register a Updated stamp in column R.

I want to leave the initial time stamp as the detail when the record was initially created unchanged.

This is the code I have so far but it is only overwriting the Time Stamp in column Q if I make a change to the records in columns A2:P100000....needs to be dynamic as I add records daily.

I tried putting the code in a Sub and calling it from the worksheet change and it is erroring on the "Target" of the following code line

If Intersect(Target, myTableRange) Is Nothing Then Exit Sub




Private Sub Worksheet_Change(ByVal Target As range)

Dim myTableRange As range
Dim myDateTimeRange As range
Dim myUpdatedRange As range

Set myTableRange = range("A2:P100000")

If Intersect(Target, myTableRange) Is Nothing Then Exit Sub

Set myDateTimeRange = range("Q" & Target.Row)
Set myUpdatedRange = range("R" & Target.Row)

If myDateTimeRange.Value = "" Then
End If

myDateTimeRange.Value = Now



End Sub

 

[Joe4]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myTableRange As Range
Dim myDateTimeRange As Range
Dim myUpdatedRange As Range

If Target.CountLarge > 1 Then Exit Sub

Set myTableRange = Range("A2:P100000")

If Intersect(Target, myTableRange) Is Nothing Then Exit Sub

Set myDateTimeRange = Range("Q" & Target.Row)
Set myUpdatedRange = Range("R" & Target.Row)

Application.EnableEvents = False

If myDateTimeRange.Value = "" Then
    myDateTimeRange.Value = Now
Else
    myUpdatedRange = Now
End If

Application.EnableEvents = True

End Sub

 

[bhalbach]

I tried putting the code in a Sub and calling it from the worksheet change and it is erroring on the "Target" of the following code line

If Intersect(Target, myTableRange) Is Nothing Then Exit Sub

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myTableRange As Range
Dim myDateTimeRange As Range
Dim myUpdatedRange As Range

If Target.CountLarge > 1 Then Exit Sub

Set myTableRange = Range("A2:P100000")

If Intersect(Target, myTableRange) Is Nothing Then Exit Sub

Set myDateTimeRange = Range("Q" & Target.Row)
Set myUpdatedRange = Range("R" & Target.Row)

Application.EnableEvents = False

If myDateTimeRange.Value = "" Then
    myDateTimeRange.Value = Now
Else
    myUpdatedRange = Now
End If

Application.EnableEvents = True

End Sub

Works like a charge in change event.

Sorry for my ignorance to VBA...

It wont work if I put it in a module as a sub!!! What am I missing?

I have some other change events that have to fire off as well and thinking it would be best if I called the sub procedures from the change event.

 

[Joe4]

A few things you need to understand:

1. Event Procedure VBA code only runs automatically when placed in the appropriate Workbook or Sheet modules. They do NOT work in General/Standard modules (like "Module1" or whatever you elect to rename it).
2. Event Procedure VBA code MUST be named a certain way. You cannot mess with the name of the procedure, or else it will not run automatically.
3. "Target" is the range in a "Worksheet_Change" or "Worksheet_SelectionChange" that "fires" the code to run (i.e. it is the range that is updated or selected). Target has no inherent meaning or value is sub other sub procedures that are not Event Procedure VBA code.

You can call other procedures that you create from your "Worksheet_Change" event procedure code. But you will probably need to pass the "Target" range to those other procedures, so they know what ranges to run on!

Or, you can just have multiple code blocks within your Worksheet_Change event procdure code to address the myriad of different things you want to do.

If you need help with this, you will need to post the other code that you have that you want to run with the "Worksheet_Change" event prcoedure.

 

[bhalbach]

A few things you need to understand:

1. Event Procedure VBA code only runs automatically when placed in the appropriate Workbook or Sheet modules. They do NOT work in General/Standard modules (like "Module1" or whatever you elect to rename it).
2. Event Procedure VBA code MUST be named a certain way. You cannot mess with the name of the procedure, or else it will not run automatically.
3. "Target" is the range in a "Worksheet_Change" or "Worksheet_SelectionChange" that "fires" the code to run (i.e. it is the range that is updated or selected). Target has no inherent meaning or value is sub other sub procedures that are not Event Procedure VBA code.

You can call other procedures that you create from your "Worksheet_Change" event procedure code. But you will probably need to pass the "Target" range to those other procedures, so they know what ranges to run on!

Or, you can just have multiple code blocks within your Worksheet_Change event procdure code to address the myriad of different things you want to do.

If you need help with this, you will need to post the other code that you have that you want to run with the "Worksheet_Change" event prcoedure.

can I run multiple worksheet change procedures on the same worksheet? Just have multiples named differently?

 

[Joe4]

can I run multiple worksheet change procedures on the same worksheet? Just have multiples named differently?

No. I refer you back to point #2 I made in my previous post. You have NO ability to change their name, or else they will not run automatically.
You can only have one "Worksheet_Change" event procedure per sheet. So if you have 3 sheets, you could have 3 procedures named "Worksheet_Change", but not in the same sheet module. Only one per sheet module.