how to put mydate into activecell as dd/mm/yyyy excel 2007 vb

[JohnnyP]

when using input box mydate and i try to put the date into the active cell it
changes from dd/mm/yyyy to mm/dd/yyyy how can stop this. Code follows

mydate = InputBox("Enter date as dd/mm/yyyy or Cancel to edit", Date, Date)
activecell=mydate

 

[Gary Keramidas]

try activecell=format(mydate,"dd/mm/yyyy")

 

[john]

probably better way to write this but as quick idea try following:

Sub DateAdd()
Dim MyDate As Date

On Error Resume Next

MyDate = InputBox("Enter date as dd/mm/yyyy or Cancel to edit", Date,
Date)

If MyDate = 0 Then
Exit Sub
Else
With ActiveCell
.NumberFormat = "dd/mm/yyyy"
.Value = MyDate
End With
End If
On Error GoTo 0
End Sub

 

[Dave Peterson]

I think that as long as you accept ambiguous date entries
(does 01/02/03 mean Jan 2, 2003 or Feb 1, 2003 or 2001-feb-03 or ...), then
you're going to have trouble with the way excel and the user's short date format
(under windows control panel) play together.

I think I'd ask for the date in a different way.

I think I'd use a userform with a calendar control (see Ron de Bruin's site):
http://www.rondebruin.nl/calendar.htm

Or even a userform with 3 different controls (day, month, year).