Macro Data Form changing date format


New Member
Feb 28, 2008
I've done a search of the forums and have been unable to find a previous thread on this. My apologies is one already exists.

I have a table of data in which two of the columns are dates. They are formatted dd/mm/yyyy as I am in UK.

I locate records in the table using the topline menu Data>Form and click Criteria, then edit as required. When done manually the dates in the form show in the correct format.

I have recorded a macro to automatically open the data form for me, and to update other sheets with amended data when I hit 'Close' then save the file.

When I use the macro to open the data form, the displayed date format changes to mm/dd/yyyy.

This happens to all data, even if I originally enter the data using the form and use the dd/mm/yyyy when I enter the data.

i want to be able to pass this file on to others to use, so I would like it to be right first time.

What am I doing wrong?:confused:


Well-known Member
Aug 21, 2005
try introduce a code in the macro to format the relevant range or columns according to your requirement.


Board Regular
Feb 20, 2009
what are your date format in windows local settings? are they correct for your locale
what code did you macro record?
post the code

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...