=Lastmodified()

[CurtJohn]

Hi,

First of all, thanks for the guy who created this macro, it almost works perfectly!
Here's the macro :

Public FunctioLastmodified(c As Range)

Lastmdified = Now()

End Function

THOUGH! I'm facing this problem :
Every time I use the filter in my table, ALL the dates are updated and I don't want this...


Can you guys please help me to fix this problem?

Thanks!

 

[CurtJohn]

Well it doesn't work, I tried every modules you did, I removed column N and created a new one, but I always have the same problem.
with :=Now() in column N, all the column behave the same, if I modify a value in column M (M2, M52 or M325), all the dates are updated in column N...

I'll stick with the old way, I will write the date manually...

Sorry guys!
Thanks

 

[Yongle]

We are happy to kepp working with you figure out what is wrong in your workbook

You can give up if you want but I suggest you try ONCE more before doing so
- this will take you 5 minutes at the outside!

Create a NEW workbook
in Sheet1 module insert

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Columns.Count > 1 Then Exit Sub
    Dim rng As Range: Set rng = Range("M2").Resize(Rows.Count - 1)
    If Not Intersect(rng, Target) Is Nothing Then Target.Offset(, 1) = Now
End Sub

Now enter
100 in M2
101 in M3
102 in M4

and you get something like this

Excel 2016 (Windows) 32 bit

	L​	M​	N​	O​
1​
2​		100​	14/06/2019 09:10​
3​		101​	14/06/2019 09:10​
4​		102​	13/06/2019 08:55​

Sheet: Sheet1

now manually amend the dates value in N2:N4

Excel 2016 (Windows) 32 bit

	L​	M​	N​	O​
1​
2​		100​	10/06/2019 09:10​
3​		101​	11/06/2019 09:10​
4​		102​	12/06/2019 08:55​
5​

Sheet: Sheet1

now amend M3 to 150
- N3 is updated
- nothing else changes which is EXACTLY what you requested

Excel 2016 (Windows) 32 bit

	L​	M​	N​	O​
1​
2​		100​	10/06/2019 09:10​
3​		150​	14/06/2019 09:11​
4​		102​	12/06/2019 08:55​
5​

Sheet: Sheet1

 

[CurtJohn]

We are happy to kepp working with you figure out what is wrong in your workbook

You can give up if you want but I suggest you try ONCE more before doing so
- this will take you 5 minutes at the outside!

Create a NEW workbook
in Sheet1 module insert

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Columns.Count > 1 Then Exit Sub
    Dim rng As Range: Set rng = Range("M2").Resize(Rows.Count - 1)
    If Not Intersect(rng, Target) Is Nothing Then Target.Offset(, 1) = Now
End Sub

Now enter
100 in M2
101 in M3
102 in M4

and you get something like this

Excel 2016 (Windows) 32 bit

	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]L[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]M[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]N[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]O[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​		100​	14/06/2019 09:10​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​		101​	14/06/2019 09:10​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​		102​	13/06/2019 08:55​

<tbody>
</tbody>

Sheet: Sheet1

<tbody>
</tbody>

now manually amend the dates value in N2:N4

Excel 2016 (Windows) 32 bit

	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]L[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]M[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]N[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]O[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​		100​	10/06/2019 09:10​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​		101​	11/06/2019 09:10​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​		102​	12/06/2019 08:55​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​

<tbody>
</tbody>

Sheet: Sheet1

<tbody>
</tbody>

now amend M3 to 150
- N3 is updated
- nothing else changes which is EXACTLY what you requested

Excel 2016 (Windows) 32 bit

	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]L[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]M[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]N[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]O[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​		100​	10/06/2019 09:10​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​		150​	14/06/2019 09:11​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​		102​	12/06/2019 08:55​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​

<tbody>
</tbody>

Sheet: Sheet1

<tbody>
</tbody>

WOW, thank you very much! It works like a charm :D
That's perfect,
Thanks, it will save me a lot of time and my sheet will be more accurate!

Have a nice day
Thanks to all of you who tried to help me ^^

 

[Yongle]

Thanks for the feedback

 

[CurtJohn]

Oops, it's still me...
Small problem, it might be because of my company security but I might have missed something...

It works, but if I save it as .xlsx I lose the module in the sheet and I need to put the module again to make it works. I don't want to do it each time I open the file...
If I save as .xlsm the macro is still written if I check with alt+f11, but nothing happens in excel... !?

What extension shoud I use?

 

[CurtJohn]

Can't edit so I'm sorry for double posting...

That's too bad, it's a security from my company, I can't run the macro when I open the file...

 

[Yongle]

That is very sad
- it is worse because you know what you are missing!

 

[Joe4]

That's too bad, it's a security from my company, I can't run the macro when I open the file...

Is it that you company will not allow you to use/save files with the "xlsm" extension, or is it that when you open the file, macros are disabled by default (but you can enable them by clicking the "Enable Macros/VBA" option that appears up by the menu bar?

If you can use VBA, but need to users to allow it, what I have seen many people do is to hide all the information/worksheets by default, and have them exposed by a Workbook_Open event. So if they do not enable macros, they won't see any of the data. So that kind of forces them to allow it if they want to use the workbook.

If the issue is the extension, a "trick" I have seen some users do is to save it as an older version of Excel, specifically, one that uses the "xls" extension, which allows VBA.

If you are not allowed to use VBA at all, well I think you are out of luck. I don't think there is any way to populate a non-changing date/time stamp automatically without the use of VBA.