0

I want to make a user type in a date in a InputBox. I load the value on a variable and check if the date is in a readable format (see code below).
If so I want to enter rows in another table with the entered date in a later stage.

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 format (see input box).

My colleagues in Denmark have a date format that is dd-mm-yyyy.
When they enter in my format they get an error message that the date isn't in the correct format.
When they type in with a minus in between the date that is entered in the table later on has a wrong value.

How do I get the code to work for both countries?

'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
Community
  • 1
  • 1
mtzng
  • 21
  • 2
  • Take a look at [this post](https://stackoverflow.com/a/12013961/1521579). It's another way of addressing the problem – Ricardo Diaz Jan 12 '21 at 18:35
  • 1
    You could generally let the users input the date in international format (`yyyy-mm-dd`). This should be interpreted well in both environments, as far as I remember. – AHeyne Jan 12 '21 at 18:40
  • In Access you can [Enter an ISO formatted date with input mask and full validation](https://www.experts-exchange.com/articles/24659/Entering-ISO-formatted-date-with-input-mask-and-full-validation-in-Microsoft-Access.html?preview=Eh7VEzgKeXU%3D). Don't know if it can be adopted for Excel. – Gustav Jan 12 '21 at 18:56

1 Answers1

0

Thanks for your suggestions. I decided to solve it simple with two input boxes for the year and the month and used the DateSerial function to put the date into the country format of the respective user.

It seems to work fine and didnt create a problem for further filtering:

YearInfo = Application.InputBox("Please enter the year you want to register the account data for. Use all four digits of the year.")    
MonthInfo = Application.InputBox("Please enter the month you want to register the account data for (as a number).")
    
TheDate = DateSerial(YearInfo, MonthInfo, 1)
mtzng
  • 21
  • 2