VBA Timestamp & Update Stamp

bhalbach

Board Regular
Joined
Mar 15, 2018
Messages
151
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,943
Office Version
  1. 365
Platform
  1. Windows
Try this:
VBA Code:
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

Board Regular
Joined
Mar 15, 2018
Messages
151
Office Version
  1. 2016
Platform
  1. Windows
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:
VBA Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,943
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Mar 15, 2018
Messages
151
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,943
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,140,999
Messages
5,703,642
Members
421,307
Latest member
morrden86

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
Top