VBA and date formatting

[hufflefry]

i have recently set up the code below:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub 'Does nothing if several cells are changed

If Not Intersect(Target, Range("A:A")) Is Nothing Then 'Only if cell changed in column A:
Target.Offset(, 1).Value = Timestamp(Target) 'Put the TimeStamp on column B
End If

End Sub

However since a new use has accessed the document i can't stop the date from formatting as MM/DD/YYYY
how do i add in a formatting to be DD/MM/YYYY? any help much appreciated!

 

[welshgasman]

I would get the new user to get his date format on his system corrected.
I would also record a macro when setting the date to dd/mm/yyyy and then use that in your code.

 

[hufflefry]

I would get the new user to get his date format on his system corrected.
I would also record a macro when setting the date to dd/mm/yyyy and then use that in your code.

All user settings are set correctly, however since their access now every user is defaulting to the mm/dd/yyyy format regardless of the formatting but only in that column. Its almost like excel thinks that its April! i have amended the code as

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub 'Does nothing if several cells are changed

If Not Intersect(Target, Range("A:A")) Is Nothing Then 'Only if cell changed in column A:
Target.Offset(, 1).Value = Timestamp(Target) 'Put the TimeStamp on column B
Range("B:B").NumberFormat = "dd/mm/yyyy" 'This will chnage the date to "23/10/2019"
End If

End Sub

If i change it to be mm/dd/yyyy it changes all dates to be that and corrects the one being updated... (hopefully that makes some sense to someone!

 

[hufflefry]

Update(ish): i tried starting from scratch again to set up and now i am receiving an error code of "Compile error: Sub or Function not defined" which then highlights the 'timestamp' part of the code.

is this a lost cause because its starting to feel like it!!

I believe that the issue is that the date is correctly formatting as DD/MM/YYYY, however before that it is registering the american version of the date being MM/DD/YYYY therefore if i change the format to be DD/MM/YYYY nothing happens, and if i change it to be MM/DD/YYYY it changes to be visually correct.
i.e. should be entering 04/11/2024, is entering 11/04/2024, if i change to DD/MM remains as 11/04, if i change to be MM/DD it will change to be 04/11/2024 ( but registers as being april.

all users now have the correct set up for date formatting, restoring old versions also has not resolved the issue, something seems to be deeper causing the issue.

 

[Joe4]

Update(ish): i tried starting from scratch again to set up and now i am receiving an error code of "Compile error: Sub or Function not defined" which then highlights the 'timestamp' part of the code.

You seem to be trying to call a User Defined Function named "TimeStamp".
Where exactly is that that in your VBA modules?
Can you post the code for that?

 

[hufflefry]

You seem to be trying to call a User Defined Function named "TimeStamp".
Where exactly is that that in your VBA modules?
Can you post the code for that?

Function Timestamp(Reference As Range)
If Reference.Value <> "" Then
Timestamp = Format(Now, "dd/mm/yyyy")
Else
Timestamp = ""
End If
End Function

This is the module i have set up, which until the weekend was working as expected.

 

[Joe4]

"Sub or Function not defined" means it is not seeing/finding your UDF.

What is the name of the VBA module where your "Worksheet_Change" event procedure is located in?
What is the name of the VBA module your "Timestamp" User Defined function is located in?.

Also note that this line of code:

Timestamp = Format(Now, "dd/mm/yyyy")

will actually return a Text value, not a Date one.

You should just use:

Timestamp = Now()

and format the range/cells to your desired format.

 

[hufflefry]

"Sub or Function not defined" means it is not seeing/finding your UDF.

What is the name of the VBA module where your "Worksheet_Change" event procedure is located in?
What is the name of the VBA module your "Timestamp" User Defined function is located in?.

Also note that this line of code:

Timestamp = Format(Now, "dd/mm/yyyy")

will actually return a Text value, not a Date one.

You should just use:

Timestamp = Now()

and format the range/cells to your desired format.

changing that line has done it! i suspect in my attempts to start from scratch i might have been on the wrong documents for a few codes meaning that they weren't actually referring to anything. thank you!

 

[Joe4]

You are welcome!

Yeah, many people don't realize that the FORMAT function in VBA and the similar TEXT function in Excel both return TEXT values (so then applying any sort of formatting to the cell would have no affect).
So it is usually best to perform the calculation, and then just format cell with your desired format (and not do it right in the calculation).