Creating a date/time stamp on document when ever there is a change made

**Youezy** · 05-07-2024, 08:29 AM

Hi there,

Thanks for taking you time and looking at my problem.

I have tired YouTube but for some reason cannot get it to work, could be something simple I'm just not that clued up with Macros/VBA.

I have 2 separate excel spread sheets, they communicate to each other though XLOOKUP.

#1 Yard Sheet - this is updated by our forklift guys out in the yard
#2 Trailer Plan - this is for the office staff to see what trailers are where and whether there empty or loaded.

i would like the trailer plan to have a date and time stamp so the office can see when it was last updated.

hope you can help?

**Bernie Deitrick** · 05-07-2024, 03:10 PM

To determine if a value has changed, every formula's result needs to be compared to its previous value. To do that, insert a new worksheet in your workbook, and name it Values. That sheet will be used to store values as values, for comparison. Then

1) Copy the code below
2) Right-Click the sheet tab of "DC TRAILER PLAN"
3) Select "View Code"
4) Paste the code into the window that appears.
5) Save the file as a macro-enabled .xlsm file.
6) Make changes as needed

Please Login or Register  to view this content.

When the linking formulas update on that sheet (whether anything changes or not), the code will compare the stored values to the current values, and if it finds any changes, it will update the date time stamp and copy all the new values into the storage sheet.

PS - when you post a workbook for help, remove the protection from the workbook: I would have made all the changes to your actual workbook but could not because of the password protection.

**Youezy** · 05-15-2024, 05:45 AM

Thanks for your help.

**Youezy** · 06-26-2024, 05:14 AM

Hi Bernie,

after many attempts to get this to work i always have the same error come up with line -

If rngC.Value <> Worksheets("Values").Range(rngC.Address).Value Then

not sure whats causing the error as VB is well out of my league.

If at all possible, could you please do it for me?
I have reattached both documents with out any locks on.
As i was unaware that the original documents had.

TEST - DC TRAILER PLAN.xlsm TEST - YARD SHEET.xlsx

Thanks for your help!

**Bernie Deitrick** · 06-26-2024, 09:52 AM

Sorry about that - I thought that both workbooks would not be open on the same machine (that you would be updating links only - the yard sheet would be used in the yard by other workers....). I guess I should have anticipated your need to test the code

Please Login or Register  to view this content.

**Youezy** · 06-26-2024, 10:32 AM

Perfect! works great. Thanks Bernie.

**Youezy** · 06-27-2024, 05:56 AM

Hi Bernie,

after it working perfect in the test document, we moved it over to the main document with all the information populated into it.

we started having issue, in the middle of our "DC TRAILER PLAN". We have a counter that counts the amount of trailers that we have with different categories..
They link to another tab called "TRAILER INFORMATION" which does the initial count and we feed the information from.

"TRAILER INFORMATION TAB" -------------------- ---------- "DC TRAILER PLAN" COUNTER
Screenshot 2024-06-27 105155.png Screenshot 2024-06-27 105136.png

When linking from the "DC TRAILER PLAN" tab to the "TRAILER INFORMATION" tab we have a run time error, showed below. -

I'm using the simple ='TRAILER INFORMATION'!F1 to feed the information across. then we get -
Screenshot 2024-06-27 104809.png

also can cause excel to crash.

It worked fine for storage trailers and bottle trailers but not stock or MT trailers.

when the error comes up and we use the debug button it shows an issue with the same line as before? -

If rngC.Value <> ThisWorkbook.Worksheets("Values").Range(rngC.Address).Value Then

Thanks again Bernie.

Creating a date/time stamp on document when ever there is a change made

LinkBack

Thread Tools

Rate This Thread

Display

Creating a date/time stamp on document when ever there is a change made

Re: Creating a date/time stamp on document when ever there is a change made

Re: Creating a date/time stamp on document when ever there is a change made

Re: Creating a date/time stamp on document when ever there is a change made

Re: Creating a date/time stamp on document when ever there is a change made

Re: Creating a date/time stamp on document when ever there is a change made

Re: Creating a date/time stamp on document when ever there is a change made

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Change Date and Time stamp when time is less than 8 AM

[SOLVED] Calculation change date time stamp

Need vba code to change date&time stamp as date format dd-mmm-yy in A:A rng

Date & Time stamp for change in value

date time stamp - not to change

Excel Status Change w/ Time/Date Stamp

individually time stamp a row when a change is made to that row

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions