VBA form reformatting a date

  • Hi All,

    I have a database that keeps a date record of training, it uses a userform to pull the info from the database, at which point the end user can adjust the date to keep the training record up to date.

    the VBA all works and it does everything I want EXCEPT that when It retrieves the dates from the database it formats it in the American style (mm/dd/yy) not the UK style (dd/mm/yy) and I cannot work out why.

    The code for the retrieval is shown below, I would like to point out that I found on the web so a big thank you to the original author

    I have included a screen shot that shows database, and you can see the date that is on the excel database is in the correct format but in the user form it has swapped to the US style.

    Any help is gratefully received


    Edited once, last by royUK: add code tags, please read the Forum Rules. ().

  • You should check the cell value for a date rather than checking the textbox contents:

    1. If IsDate(Cells(i + 1, j).Value) thenUserForm1.Controls("TextBox" & j).Value = Format(Cells(i + 1, j).Value, "dd/mmm/yyyy")elseUserForm1.Controls("TextBox" & j).Value = Cells(i + 1, j).Valueend if

    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why