VBA: Check date value depending on country specific format

Question

Hej guys,

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
                
        Do
            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"
                End
                
            End If
            
        Else
        
            TheDate = Format(DateValue(TheString), "DD.MM.YYYY")
            
        End If
        
        Loop Until IsDate(TheString) And cd = 1

Answer 1

等待大神答复