Macro code changing date format

[Beneindias]

Hi to all of you,

I have an excel sheet with an Inputbox, where i'm asker to put the number of the month, the year and a third value.

Problem is, it was working as intended during 2023, but now, it's swaping the month for the day.

I'm in Europe, we use dd/mm/yyyy and I'm trying to make the file for February 2024 (month 2).

But, as I do this, the date in cell D1 is always transformed as "02/01/2024" when I want "01/02/2024".
If I go to cell D1 and manualy enter "01/02/2024" it stays how it should.

I don't think that the problem is in the macro code, but I can't find a solution to this.
Cell is formated as Date dd/mm/yyyy.

My code is this one:

Public Sub MyInputBox()

    Dim MyInputMonth As String
    Dim MyInputYear As String
    Dim MyInputPago As String
    Dim NumericInputMonth As Boolean
    Dim NumericInputYear As Boolean
    Dim NumericInputPago As Boolean
    Dim MyInputDate As String

    
    'Box to user input
    'Month number
    MyInputMonth = InputBox("Escrever número do novo mês", "Iniciar novo mês", "Número do novo mês")
    NumericInputMonth = IsNumeric(MyInputMonth)
    
    'Year number
    MyInputYear = InputBox("Escrever número do ano", "Iniciar novo mês", "Número do ano")
    NumericInputYear = IsNumeric(MyInputYear)
    
    'Value
    MyInputPago = InputBox("Adicionar valor pago", "Valor pago", "Valor pago!")
    NumericInputPago = IsNumeric(MyInputPago)

    
    'Checks if month number is between 1 and 12
    If NumericInputMonth Then
        If MyInputMonth >= 1 And MyInputMonth <= 12 Then
        
            MyInputDate = 1 & "-" & MyInputMonth & "-" & MyInputYear
    
            'Calls macro to delete table cells
            Call Clearcells
    
            'Adds date to cell D1
            Range("D1").Value = MyInputDate
            
            Range("G53").Value = MyInputPago
            

                    
        Else
            'GoTo errHand
            MsgBox "Número de mês mal introduzido"
        End If
    Else
        'GoTo errHand
        MsgBox "Tem que introduzir número do mês"
    End If
    
    Exit Sub
'errHand:
    'MsgBox "Número de mês mal introduzido"
    
End Sub

Thank you all in advance

 

[MARK858]

We'll try the easy way first, try making the changes in red and see if you get the correct results

            MyInputDate = MyInputYear & "-" & MyInputMonth & "-" & 1
    
            'Calls macro to delete table cells
            Call Clearcells
    
            'Adds date to cell D1
            Range("D1").Value = MyInputDate
            Range("D1").NumberFormat = "dd/mm/yyyy"
            
            Range("G53").Value = MyInputPago

 

[dmt32]

Hi
you are posting a string date to your range so excel likely to default to USA date format.
To overcome this you can use one of the available Type Conversion Functions to coerce your string date to date.

See if this update to your code helps you

Public Sub MyInputBox()
    
    Dim MyInputMonth As Variant
    Dim MyInputYear As Variant
    Dim MyInputPago As Variant
    
    Dim MyInputDate As Date
    
    'Box to user input
    'Month number
    Do
        MyInputMonth = InputBox("Escrever número Do novo mês", "Iniciar novo mês", "Número Do novo mês")
        'cancel pressed
        If StrPtr(MyInputMonth) = 0 Then Exit Sub
    Loop Until Val(MyInputMonth) >= 1 And Val(MyInputMonth) <= 12
    
    'Year number
    Do
        MyInputYear = InputBox("Escrever número Do ano", "Iniciar novo mês", "Número Do ano")
        'cancel pressed
        If StrPtr(MyInputYear) = 0 Then Exit Sub
    Loop Until MyInputYear Like "####"
    
    'Value
    Do
        MyInputPago = InputBox("Adicionar valor pago", "Valor pago", "Valor pago!")
        'cancel pressed
        If StrPtr(MyInputPago) = 0 Then Exit Sub
    Loop Until IsNumeric(MyInputPago)
    
    MyInputDate = CDate(1 & "/" & MyInputMonth & "/" & MyInputYear)
    
    'Calls macro to delete table cells
    Call ClearCells
    
    'Adds date to cell D1
    With Range("D1")
        .Value = MyInputDate
        .NumberFormat = "dd/mm/yyyy"
    End With
    
    Range("G53").Value = Val(MyInputPago)
    
End Sub

I have used CDate function which uses your regional settings & should hopefully, produce correct result but there are other alternatives you can try if still have the same issue.

Dave

 

[Beneindias]

We'll try the easy way first, try making the changes in red and see if you get the correct results

            MyInputDate = MyInputYear & "-" & MyInputMonth & "-" & 1
   
            'Calls macro to delete table cells
            Call Clearcells
   
            'Adds date to cell D1
            Range("D1").Value = MyInputDate
            Range("D1").NumberFormat = "dd/mm/yyyy"
           
            Range("G53").Value = MyInputPago

This solution worked.

But I still find it strange, because my code was working fine in 2023.

Thanks for your help

 

[Beneindias]

Hi
you are posting a string date to your range so excel likely to default to USA date format.
To overcome this you can use one of the available Type Conversion Functions to coerce your string date to date.

See if this update to your code helps you

Public Sub MyInputBox()
   
    Dim MyInputMonth As Variant
    Dim MyInputYear As Variant
    Dim MyInputPago As Variant
   
    Dim MyInputDate As Date
   
    'Box to user input
    'Month number
    Do
        MyInputMonth = InputBox("Escrever número Do novo mês", "Iniciar novo mês", "Número Do novo mês")
        'cancel pressed
        If StrPtr(MyInputMonth) = 0 Then Exit Sub
    Loop Until Val(MyInputMonth) >= 1 And Val(MyInputMonth) <= 12
   
    'Year number
    Do
        MyInputYear = InputBox("Escrever número Do ano", "Iniciar novo mês", "Número Do ano")
        'cancel pressed
        If StrPtr(MyInputYear) = 0 Then Exit Sub
    Loop Until MyInputYear Like "####"
   
    'Value
    Do
        MyInputPago = InputBox("Adicionar valor pago", "Valor pago", "Valor pago!")
        'cancel pressed
        If StrPtr(MyInputPago) = 0 Then Exit Sub
    Loop Until IsNumeric(MyInputPago)
   
    MyInputDate = CDate(1 & "/" & MyInputMonth & "/" & MyInputYear)
   
    'Calls macro to delete table cells
    Call ClearCells
   
    'Adds date to cell D1
    With Range("D1")
        .Value = MyInputDate
        .NumberFormat = "dd/mm/yyyy"
    End With
   
    Range("G53").Value = Val(MyInputPago)
   
End Sub

I have used CDate function which uses your regional settings & should hopefully, produce correct result but there are other alternatives you can try if still have the same issue.

Dave

Thanks for your help.

Didn't tried your code, because the other solution was easier to implement in various files and it worked.

Thank for your help

 

[MARK858]

This solution worked.

But I still find it strange, because my code was working fine in 2023.

Thanks for your help

You're welcome, your code would have worked for certain if the day was higher than the 12th of the month... maybe that was how it was setup in 2023.

If you were getting the date from a cell or if it was a string in VBA like "01/02/2024" you would have needed to do a type conversion like dmt32 posted but because you were getting the numbers individually I just changed the order so it was in the International date format (yyyy-mm-dd), which VBA doesn't appear to convert to US format (mm/dd/yyyy) when it puts it into the cell.

 

[Beneindias]

You're welcome, your code would have worked for certain if the day was higher than the 12th of the month... maybe that was how it was setup in 2023.

If you were getting the date from a cell or if it was a string in VBA like "01/02/2024" you would have needed to do a type conversion like dmt32 posted but because you were getting the numbers individually I just changed the order so it was in the International date format (yyyy-mm-dd), which VBA doesn't appear to convert to US format (mm/dd/yyyy) when it puts it into the cell.

Learning something new every day.
Thanks for the explanation, because, honestly, I wasn't understanding why it was working with your solution.

Thanks for thar