My timestamp functions are being reseting to current date and time when the file is opened by another user or when the data is refreshed

cerigoman

New Member
Joined
Jan 30, 2021
Messages
2
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Dear all . I hope you can help me with the following problem :

Have created 2 timestamp functions with VBA , one for the date and one for the time in order to use them when a specific value is changed in a specific cell . For example when data is changed at cell C then cells A & B are automatically updated with the date and time respectvilly . Everything works and looks ok but when i am sending the file to another person, (as xlsm file) , when the other person opens file the timestamp cells are being changed to the date and time the person opened the file . Have tried to overcome the issue by locking the cells but in vain.

At first i thought that it is happening because the other person has the marco security enabled and when they disable it in order to open the file it resets the function (maybe it plays some role) but even though it does nt make sense as is cancelling the meaning of the timestamp itself.

Below the 2 functions :

1]

Function TimestampFORDATE(Reference As Range)
If Reference.Value <> "" Then
TimestampFORDATE = Format(VBA.DateTime.Now, "dd-mm-YYYY")
Else
TimestampFORDATE = ""
End If
End Function

2]
Function TimestampFORTIME(Reference As Range)
If Reference.Value <> "" Then
TimestampFORTIME = Format(VBA.DateTime.Now, "HH:MM")
Else
TimestampFORTIME = ""
End If
End Function

ΒΙΚΤΩΡΙΑ ΦΟΡΜΑ .xlsm
B
1330-01-2021
ΜΗΝΑΣ
Cell Formulas
RangeFormula
B13B13=TimestampFORDATE(D13)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A13:O102Expression=$D13="ΑΛΛΑΓΗ ΠΡΟΓΡΑΜΜΑΤΟΣ"textNO
A13:O102Expression=$D13="ΑΛΛΑΓΗ ΣΤΟΙΧΕΙΩΝ"textNO
A13:O102Expression=$D13="ΔΙΑΦΟΡΑ"textNO
A13:O102Expression=$D13="RETENTION"textNO
A13:O102Expression=$D13="ΠΑΡΑΠΟΝΟ"textNO
A13:O102Expression=$D13="ΕΝΗΜΕΡΩΣΗ"textNO
A13:O102Expression=$D13="ΠΛΗΡΩΜΗ ΗΡΩΝ"textNO
A13:O102Expression=$D13="ΣΥΜΒΑΣΗ"textNO
A13:O102Expression=$D13="ΠΛΗΡΩΜΗ ΑΛΛΩΝ"textNO
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

cerigoman

New Member
Joined
Jan 30, 2021
Messages
2
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Thanks in advance and sorry for the greek language cells :)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,181
Messages
5,640,640
Members
417,159
Latest member
Mayozero

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