VBA and date formatting

hufflefry

New Member
Joined
Oct 18, 2024
Messages
7
Office Version
  1. 365
Platform
  1. Windows
i have recently set up the code below:

VBA Code:
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!
 
Last edited by a moderator:

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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.
 
Upvote 0
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
VBA Code:
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!
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
"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:
VBA Code:
Timestamp = Format(Now, "dd/mm/yyyy")
will actually return a Text value, not a Date one.

You should just use:
VBA Code:
Timestamp = Now()
and format the range/cells to your desired format.
 
Upvote 0
Solution
"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:
VBA Code:
Timestamp = Format(Now, "dd/mm/yyyy")
will actually return a Text value, not a Date one.

You should just use:
VBA Code:
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!
 
Upvote 0
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).
 
Upvote 0

Forum statistics

Threads
1,224,271
Messages
6,177,619
Members
452,786
Latest member
k3calloway

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
Back
Top