Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Set different timestamp row cells entries in different data range zones in one same worksheet.

0

Please see the attached Excel doc 'Auto Timestamp example 2'.

Is it possible to have 3 (or more) data ranges with its own 3 (or more) respective auto timestamp data entries?

I have marked these 3 areas in different colors.

And in this one worksheet, let's say when data is entered into the yellow data range, can it be set that only the yellow zone timestamp cells are registered but not the timestamp cells of pink and blue.

Thankyou in advance.

Answer
Discuss

Answers

0
Selected Answer

Viktor

The attached revised file creates timestamps for each of the three coloured rergions in your file. 

It modifies the code I gave you for your previous question Automatically Timestamp Data Entries in row instead of column. Crucially it takes the line which stops the rest of the macro running:

'Check if the changed cell is in the data table or not.
If Intersect(Target, myTableRange) Is Nothing Then Exit Sub
and changes it to:
If Not Intersect(Target, myTableRange) Is Nothing Then
<< code to do if test is met>
End If
and that code can be repeated for your 3 regions as follows:
Private Sub Worksheet_Change(ByVal Target As Range)
' Modified macro for Timestamp Data in multiple regions
'   TeachExcel.com

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

'Stop events from running
Application.EnableEvents = False

' ### do timestamps for FIRST region
'Your data table range
Set myTableRange = Range("B4:J12")

'Check if the changed cell is in the data tabLe or not.
If Not Intersect(Target, myTableRange) Is Nothing Then
    'Set row for the Input date/time
    Set myDateTimeRange = Cells(2, Target.Column)
    'Row for last updated date/time
    Set myUpdatedRange = Cells(1, Target.Column)

    'Determine if the input date/time should change
        If myDateTimeRange.Value = "" Then
            myDateTimeRange.Value = Now
        End If
    'Update the updated date/time value
    myUpdatedRange.Value = Now

End If

' ### do timestamps for SECOND region
Set myTableRange = Range("B17:J25")

If Not Intersect(Target, myTableRange) Is Nothing Then
    Set myDateTimeRange = Cells(15, Target.Column)
    Set myUpdatedRange = Cells(14, Target.Column)

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

End If

' ### do timestamps for THIRD region
Set myTableRange = Range("B30:J38")

If Not Intersect(Target, myTableRange) Is Nothing Then
    Set myDateTimeRange = Cells(28, Target.Column)
    Set myUpdatedRange = Cells(27, Target.Column)

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

End If

'Turn events back on
Application.EnableEvents = True

End Sub
I've used the same variables and just redefinnd them for the different regions (address changes in bold). The second and third sections have comments removed. Note that Application.ScreenUpdating is moved to the start and end of the code (so it needs to appear only once) and that I also corrected your spelling to "Input Time" in A2 etc. in the worksheet.

If instead you want to disable one or more of those sections (say to have only blue section working), you can just comment it out (you can select it and use the comment button on VB Explorer). 

Hope this helps. Kindly confirm (/select Answer) if so.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login