date format problem

[brianfosterblack]

I am using this code in a macro. The result from the code gives me the date mm/DD/yyyy although my computer is set to the English format of dd/MM/yyyy
Can you please advise what I have done wrong?

    Application.ScreenUpdating = False
    ActiveSheet.Unprotect
    Range("AC2000").Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Value = InputBox("Enter Date in this format - dd/MM/yyyy")
    If Not IsDate(ActiveCell.Value) Then
    Application.ScreenUpdating = True
    ActiveSheet.Protect
    MsgBox ("You have not entered a valid date in the Start Date. " & vcCr & _
    "Please correct this")
    ActiveCell.Value = ""
    Exit Sub
    End If

 

[RoryA]

InputBox returns a string, which will be interpreted in VBA's default US settings unless you tell it otherwise. You can use:

ActiveCell.Value = CDate(InputBox("Enter Date in this format - dd/MM/yyyy"))

but you'll need an error handler in case they enter something invalid.

 

[brianfosterblack]

ActiveCell.Value = CDate(InputBox("Enter Date in this format - dd/MM/yyyy"))

Thank you, this works perfectly. Appreciate the help

 

[Joe4]

One thing that you can do to help eliminate data entry errors is to use a Calendar Date Picker instead of relying on the user to manually enter the date correctly.

There are a few different ways you can do that. Here are a few options (there are others, if you do a Google search on "Excel Calendar Date Picker"):

Home | Ron de Bruin Excel Automation

Welcome to https://www.rondebruin.nl

www.rondebruin.nl

Use This Free Excel Date Picker to Enter Dates on Worksheet

Use this Excel Date Picker to select dates for a worksheet cell, or create a quick month or year calendar to sheet. Code is unlocked, to see how it works.

www.contextures.com

 

[brianfosterblack]

One thing that you can do to help eliminate data entry errors is to use a Calendar Date Picker instead of relying on the user to manually enter the date correctly.

There are a few different ways you can do that. Here are a few options (there are others, if you do a Google search on "Excel Calendar Date Picker"):

Home | Ron de Bruin Excel Automation

Welcome to https://www.rondebruin.nl

www.rondebruin.nl

Use This Free Excel Date Picker to Enter Dates on Worksheet

Use this Excel Date Picker to select dates for a worksheet cell, or create a quick month or year calendar to sheet. Code is unlocked, to see how it works.

www.contextures.com

Thanks for this Joe4. I am saving the links and will study up on it tomorrow. Sounds like a much better idea.

 

[Joe4]

You are welcome.
Glad we were able to help!