I want to make a user type in a date in a InputBox. I will load the value on a variable and check if the date is in a readable format (see code below). If so I want enter rows in another table with the entered date from the user in a later stage.
The problem is that I am located in Germany and the date format on my machine is dd.mm.yyyy so I asked the user to type in this specific format (see input box). But my colleagues in Denmark have a date format that is dd-mm-yyyy. So when they enter in my format they get an error message that the date isnt in the correct format. When they type in with a minur in between it works but the date that is entered in the table later on has a complete wrong value. how do I get the code to work for both countries? Can you help?
'msgbox for which period this information is supposed to be
Dim TheString As String, TheDate As Date
TheString = Application.InputBox("Please enter a date in the following format DD.MM.YYYY. Start with the first day of the month.")
cd = Format(Day(TheString), 0)
If Not IsDate(TheString) Or cd <> 1 Then
If MsgBox("You have entered the date in a wrong format. Do you want to try again selecting the date for these information?", vbCritical + vbYesNo, "DATE FORMAT INCORRECT") = vbNo Then
MsgBox "The data transformation has been stopped.", vbInformation, "STOP TRANSFER"
TheDate = Format(DateValue(TheString), "DD.MM.YYYY")
Loop Until IsDate(TheString) And cd = 1