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


J

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
 
Ad

Advertisements

J

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
 
Ad

Advertisements

D

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).
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top