Worsheet_change Multiple uses same sheet

LearningXL · May 22nd 2020

Good morning everyone,

i have the following code but i would like to run this code for two different areas of the worksheet, ive tried different variations of this code but nothing worked so far.

Code

Private Sub Worksheet_Change(ByVal Target As Range)
'Timestamp Data
Dim myTableRange As Range
Dim myDateTimeRange As Range
Dim myUpdatedRange As Range

'Your data table range
Set myTableRange = Range("FB2:FD10000") '***Also what this range as well ("BI2:BL10000")

'Check if the changed cell is in the data tabe or not.
If Intersect(Target, myTableRange) Is Nothing Then Exit Sub

'Stop events from running
Application.EnableEvents = False

'Column for the date/time
Set myDateTimeRange = Range("FA" & Target.Row) '***Also what this ("BG" & Target.Row)

'Column for last updated date/time
Set myUpdatedRange = Range("FE" & Target.Row) '***Also what this ("BH" & Target.Row)

'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

'Turn events back on
Application.EnableEvents = True

End sub

Display More

**royUK** · May 22nd 2020

Attach a small example workbook

LearningXL · May 22nd 2020

here you go

**royUK** · May 22nd 2020

Why have two Tables? Data should be in one Table.

It doesn't make sense, I have no idea what you are doing.

LearningXL · May 22nd 2020

that sheet is being used as a database sheet, so it has quite a few databases on it

**royUK** · May 22nd 2020

It's best to have one database per sheet.

LearningXL · May 22nd 2020

but thats not how i want it, i want them all on one sheet

**royUK** · May 22nd 2020

That's why you are having problems.

You won't learn if you can't take advice. Good luck.

LearningXL · May 22nd 2020

wow royuk, i was not being aggressive. you asked me question and i answered you. i can here for help on some code, i did not expect to have my hand slapped and sent to the corner

LearningXL · May 22nd 2020

would it be possible to have some further help please

**royUK** · May 23rd 2020

I tried to help, but it seems you don't want to listen.

**royUK** · May 23rd 2020

You simply need to define the range using UNion

Code

'Your data table range
Set myTableRange = Union(Range("FB2:FD10000"), Range("BI2:BL10000"))

I will gurantee that you will soon find other issues and that the "databases" will not be user friendly

LearningXL · May 23rd 2020

Thank you RoyUk for your help only that code updates both "FF" & "BH" at the same time, i would only like the ranges that are updated to be updated not both at the same time, they would need to be independant.

**royUK** · May 23rd 2020

Your ranges for the two"databases" are incorrect.

Also, in your code you switch off EnableEvents twice, but only restore it once. EnableEvents will not restore itself unless you close the workbook.

I have completely re-written your code to make it work

Code

Private Sub Worksheet_Change(ByVal Target As Range)
'Timestamp Data
Dim myTableRange As Range
Dim myDateTimeRange As Range
Dim myUpdatedRange As Range
Set myTableRange = Union(Range("FA2:FE10000"), Range("BG2:BL10000"))
Select Case True
'Check if the changed cell is in the data tabe or not.
Case Not Intersect(Target, myTableRange.Areas(2)) Is Nothing
'Column for the date/time
Set myDateTimeRange = Cells(Target.Row, myTableRange.Areas(2).Columns(1).Column)
Set myUpdatedRange = Cells(Target.Row, myTableRange.Areas(2).Columns(2).Column)
Case Not Intersect(Target, myTableRange.Areas(1)) Is Nothing
Set myDateTimeRange = Cells(Target.Row, myTableRange.Areas(1).Columns(1).Column)
Set myUpdatedRange = myDateTimeRange.Offset(, 4)
Case Else: Exit Sub
End Select
'Stop events from running
Application.EnableEvents = False
'Determine if the input date/time should change
If myDateTimeRange.Value = "" Then myDateTimeRange.Value = Now
'Update the updated date/time value
myUpdatedRange.Value = Now
Application.EnableEvents = True
End Sub

Display More

LearningXL · May 24th 2020

Thank you RoyUk, it worked like a dream, however i have another question regarding this code. if i want to add another area for adding time added and Updated, is it this area only that i amend

Code

'example adding ,Range("DH2:DK100000") on line below
Set myTableRange = Union(Range("FA2:FF10000"), Range("BG2:BL10000"))
    Select Case True
        'Check if the changed cell is in the data tabe or not.
    Case Not Intersect(Target, myTableRange.Areas(2)) Is Nothing '*********changing (2)) to (3))
        'Column for the date/time
        Set myDateTimeRange = Cells(Target.Row, myTableRange.Areas(2).Columns(1).Column)
        Set myUpdatedRange = Cells(Target.Row, myTableRange.Areas(2).Columns(2).Column)
'adding to above
        Set myDateTimeRange = Cells(Target.Row, myTableRange.Areas(3).Columns(1).Column)
        Set myUpdatedRange = Cells(Target.Row, myTableRange.Areas(3).Columns(3).Column)

Display More

**royUK** · May 24th 2020

You need to extend the Union then add another Case to the Select Case Statement. This will get more complicated because you ranges don't even match formats, the dates are in different columns. Also, as I said you didn't even define the ranges correctly

LearningXL · May 24th 2020

in what way did in not define ranges correctly?? please dont take that question the wrong way im just asking

LearningXL · May 24th 2020

Code

 Set myTableRange = Union(Range("FA2:FF10000"), Range("BG2:BL10000"), Range("DH2:DK100000"))
    Select Case True
        'Check if the changed cell is in the data tabe or not.
    Case Not Intersect(Target, myTableRange.Areas(3)) Is Nothing
        'Column for the date/time
        Set myDateTimeRange = Cells(Target.Row, myTableRange.Areas(2).Columns(1).Column)
        Set myUpdatedRange = Cells(Target.Row, myTableRange.Areas(2).Columns(2).Column)
        
    Case Not Intersect(Target, myTableRange.Areas(1)) Is Nothing
        Set myDateTimeRange = Cells(Target.Row, myTableRange.Areas(1).Columns(1).Column)
        Set myUpdatedRange = myDateTimeRange.Offset(, 4)
    Case Else: Exit Sub

Display More

LearningXL · May 24th 2020

or do you mean like this

Code

Set myTableRange = Union(Range("FA2:FF10000"), Range("BG2:BL10000"), Range("DH2:DK100000"))
    Select Case True
        'Check if the changed cell is in the data tabe or not.
    Case Not Intersect(Target, myTableRange.Areas(3)) Is Nothing
        'Column for the date/time
        Set myDateTimeRange = Cells(Target.Row, myTableRange.Areas(2).Columns(1).Column)
        Set myUpdatedRange = Cells(Target.Row, myTableRange.Areas(2).Columns(2).Column)
       '******Add Line here????
    Case Not Intersect(Target, myTableRange.Areas(1)) Is Nothing
        Set myDateTimeRange = Cells(Target.Row, myTableRange.Areas(1).Columns(1).Column)
        Set myUpdatedRange = myDateTimeRange.Offset(, 4)
        '******Add Line here????
    Case Else: Exit Sub

Display More

LearningXL · May 24th 2020

sorry RoyUK, i understand your code now, many many many thanks for your help on this, i know this is now how you wanted me to do it but it works perfectly. thank you

*****SOLVED******

Worsheet_change Multiple uses same sheet

Participate now!

Share

Users Viewing This Thread