VBA issue with date format

[Goodangel]

Hello,

I have an issue with a date format in an excel file.

When I run the report (excel is connected with Noetix and Oracle e business suite) I have results with no problem. Currently I am in Vienna, Austria.

However when a colleague of mine in Vietnam run the same report then his has en error. The VBA code that is running is the following. The date format in my laptop (windows 7) is dd/mm/yyyy and as I saw in my colleague (windows 8 or 10) is mm/dd/yyyy.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$D$7" Then

    user_name = InputBox("Please enter your Oracle user name:")
    If user_name = "" Then End
    Oracle_password = InputBox("Please enter your Oracle password:")
    If Oracle_password = "" Then End
    end_date = Format(InputBox("Please enter the period end date in the format dd/mm/yyyy (eg 30/06/2013):", Default:=Format(WorksheetFunction.EoMonth(Date, -1), "dd/mm/yyyy")), "dd/mm/yyyy")
    If end_date = "" Then End
    
    Range("N10").Value = user_name
    Range("N11").Value = Oracle_password
    Range("D7").Value = end_date

End If

End Sub

Is the problem coming from the date format and how can I solve it so even if she has different date format to eventually run the report with the one I have.

Thank you in advance for the help.

Regards,

[Paul_Hossler]

You can use the[#] icon to add [CODE] ... [/CODE] tags and paste your macro between then

[MINCUS1308]

This ran without error on my windows 10 machine with the format mm/dd/yyyy.

While this probably isn't the best solution you may try adding the following lines
add this so that it is the first line of your sub

On Error Resume Next

add this just above the 'End Sub' line

On Error GoTo 0

[Goodangel]

Hello Paul,


Thank you for your reply.

As my VBA knowledge is not the best, what you are actually suggesting as I didn't fully understand.

Thank for the help and sorry for the inconvenience.

Thanks,

Evan

[MINCUS1308]

Everyone starts somewhere
Just copy and paste this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
    If Target.Address = "$D$7" Then
         
        user_name = InputBox("Please enter your Oracle user name:")
        If user_name = "" Then End
        Oracle_password = InputBox("Please enter your Oracle password:")
        If Oracle_password = "" Then End
        end_date = Format(InputBox("Please enter the period end date in the format dd/mm/yyyy (eg 30/06/2013):", Default:=Format(WorksheetFunction.EoMonth(Date, -1), "dd/mm/yyyy")), "dd/mm/yyyy")
        If end_date = "" Then End
         
        Range("N10").Value = user_name
        Range("N11").Value = Oracle_password
        Range("D7").Value = end_date
         
    End If
On Error GoTo 0
End Sub

[p45cal]

I strongly suspect international settings are the culprit here. In the US/UK the day portion of a format string is d, in France it's j, in Vietnam I haven't the foggiest.
While I have little experience of international problems with vba code, and there may be a much easier solution than the following, have a go with:
Instead of:

end_date = Format(InputBox("Please enter the period end date in the format dd/mm/yyyy (eg 30/06/2013):", Default:=Format(WorksheetFunction.EoMonth(Date, -1), "dd/mm/yyyy")), "dd/mm/yyyy")

try:

myDateFormat = String(2, Application.International(xlDayCode)) & "/" & String(2, Application.International(xlMonthCode)) & "/" & String(4, Application.International(xlYearCode))
end_date = Format(InputBox("Please enter the period end date in the format dd/mm/yyyy (eg 30/06/2013):", Default:=Format(WorksheetFunction.EoMonth(Date, -1), myDateFormat)), myDateFormat)

See also:
https://www.rondebruin.nl/win/s9/win012.htm
http://www.oaltd.co.uk/ExcelProgRef/Ch22/default.htm
https://bettersolutions.com/excel/ma...ernational.htm
https://www.mrexcel.com/forum/excel-...ormatting.html
https://msdn.microsoft.com/en-us/lib...ffice.12).aspx

[Paul_Hossler]

Hello Paul,

As my VBA knowledge is not the best, what you are actually suggesting as I didn't fully understand.

Thank for the help and sorry for the inconvenience.

Evan

Evan, no problem. Just a non-VBA suggestion / request that people use the [#] icon to add the CODE tags since it formats a macro and sets it off to make it easier for people to see

[Paul_Hossler]

I'm sure P45cal is correct and it is a Localization issue

If the value is recognized by Excel as a Date, then the displayed value will adjust

I made a few changes to your macro, but look at the CDate function

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim user_name As String, Oracle_Password As String, end_date As String
     
    If Target.Cells(1, 1).Address <> "$D$7" Then Exit Sub
         
    user_name = InputBox("Please enter your Oracle user name:")
    If user_name = "" Then Exit Sub '   don't think you meant 'End'
    
    Oracle_Password = InputBox("Please enter your Oracle password:")
    If Oracle_Password = "" Then Exit Sub
    
    'this is a string
    end_date = Format(InputBox("Please enter the period end date in the format dd/mm/yyyy (eg 30/06/2013):", Default:=Format(WorksheetFunction.EoMonth(Date, -1), "dd/mm/yyyy")), "dd/mm/yyyy")
    If end_date = "" Then Exit Sub
    
    Range("N10").Value = user_name
    Range("N11").Value = Oracle_Password
            
    If IsDate(Range("D7").Value) Then
        Range("D7").Value = CDate(Range("D7").Value)   '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    End If
     
End Sub

Just by changing the Location of where the PC thinks it is, the format of the date value changes

US.JPGVietnam.JPG

[Goodangel]

Hello guys,


Thank you all for your support!I changed the excel report I have based on p45cal suggestion. I could run it but my colleague in Vietnam again couldn't..I will have a call with her and check the system date format. I will update accordingly!

Thanks once again.

Regards,

Evan

[Paul_Hossler]

Find out what the PC regions are set to also

That'll be helpful

[Goodangel]

Hello guys,


I have the date regions which I attach them here. (I also atatch my PC region settings for your information). It is nto totally the same I think. She managed to run the excel report at least but with small issues. I hereby attach my PC settings My PC settings.jpg And in the following image she is showing me her PC date options. options general.jpgadditional settings.jpgWhat option should be the best for the VBA to run correctly? it seems that this excel file I have is totally dependent on the date. I think because the data are retrieved from Oracle where the date format is dd/mm/yyyy.

Thanks for your continuous help.

Regards,

Evan

[mdmackillop]

I don't know if the Short Date format can be determined from the PC. I would guess so. Meantime, give this a try.

Option Explicit
Sub Test()
Dim Dt As Date
Dt = InputBox("Enter date in " & DtF)
MsgBox Format(Dt, "ddd mmm dd yyyy")  'Format as required
End Sub


Function DtF()
Select Case CDate(36891)
Case "31/12/2000"  'UK
DtF = "dd/mm/yy"
Case "12/31/2000"  'US
DtF = "mm/dd/yy"
Case "2000/31/12"
DtF = "yy/dd/mm"
Case "2000/12/31"
DtF = "yy/mm/dd"
End Select
End Function

[Goodangel]

Thank you for your help mdmackillop but I have used p45cal solution which worked eventually. At least my colleague has results even without the same date format as I have. (I have dd mm yyyy and she now has d mm yyyy or something similar..)
There are side effects though because of the following code I think. When she puts the password and user name, she can see the results in the specific cells that are mentioned below..I tried to change the reference cell but the excel is not working..so she has to manually change these cells with dates (luckily in these cells only dates appears..). If there is also a solution to the below or you need more info for a solution let me know! I appreciate your help!! Regards, Evan
Range("N10").Value = user_name
Range("N11").Value = Oracle_password
Range("D7").Value = end_date