Date format not consistent when storing or editing date cells using userform

Hi all,

I am building a userform that is capable of storing data in a sheet (called 'Database') in the same file. there is also functionality to search values per columns on the userform using a listbox to be able to select a row and then edit and submit the data again. This is where some problems arise regarding the date formatting.

I have 4 textboxes where the date is selected using a calendar. I want it to be in the format DD-MM-YYY. When i use my calendar to select a date it tastes the right date in the right format in the textbox, however if i the save my data it changes to MM-DD-YYYY if i look in the database. It does this only if it's possible: so let's say i put it in 7-9-2023, it changes to 9-7-2023, but 25-9-23 stays 25-9-2023. This is the first part of the problem. I have checked with changing the language / regional settings of the operating system but even if theyre set to DD-MM-YYYY it still changes it. Also changing the Excel language does not change anything. Is there anyway to make sure it always stores the right date/date format when saving the data?

Here is the part of the code that saves the data to the sheet after filling them on the userform. textboxes with the dates are txtWlevink, txtGlevink, txtWlevver, txtGLevver:

Please Login or Register  to view this content.

The second question is regarding the edit part of the userform. When trying to edit a row, it copies the data back to the userform when clicking edit after selecting a row in the listbox on the userform. When the dates are 'possible' in the US notation (such as 9-2-2023) are copied back as a 5 character number instead of the date. Is there any way to fix this?

this part of the code is responsible for editing:

Please Login or Register  to view this content.

basically the question is: How can i fix this so that when the computer and Excel are set to the Dutch format (DD-MM-YYYY) that the Database sheet and the userform only use that format? Different people need to use this file so even better would be if it always works when someone uses it even with different settings.


by the way:
i tried using

Please Login or Register  to view this content.

But that does not work

Hi, thanks for the quick reply. However if i use it now and select dates in the beginning of september 2023. it the stores completely different dates in 2022. i think this is due to the not being a 0 in front. So 5-9-2023 got stored as 25-09-2022. do you know how i can make sure that that does not happen? The edit function works perfectly by the way so a big thanks for that!

i forgot to add that probably the easiest fox, is that if you click on a date on the calendar it automatically puts a 0 in front of the (single digits) days and months. This is the code that is used by the calendar:

Please Login or Register  to view this content.

i edit the the day click sub to this:

Please Login or Register  to view this content.

And now everything works perfectly! It seems i forgot one small thing though.

this line of code in the submit Sub:

Please Login or Register  to view this content.

is used to check the latest time some user edited/submitted a row. however now it is, based on the system. I tried too use the mdteDateValue but can't get it to work. Do you have any idea?