Format issues...

[DaveyJones]

I am using the folllowing code to get the user to input data in a msgbox and
then the data is stored in the appropriate cell.

Sheets("Milestone").Select
Range("C3").Select
Message = "Enter Customer Name..." ' Set prompt.
MyValue = InputBox(Message, Default)
ActiveCell = MyValue

Range("C4").Select
Message = "Please Enter MS Handover Date..." ' Set prompt.
MyValue = InputBox(Message, Default)
ActiveCell = MyValue

Range("C5").Select
Message = "Please Enter Actual Handover Date..." ' Set prompt.
MyValue = InputBox(Message, Default)
ActiveCell = MyValue

Range("C6").Select
Message = "Please Enter Demo Date..." ' Set prompt.
MyValue = InputBox(Message, Default)
ActiveCell = MyValue

Range("C7").Select
Message = "Please Enter Test & Adjust Date..." ' Set prompt.
MyValue = InputBox(Message, Default)
ActiveCell = MyValue

Most of them work fine, except the MS handover Date and Actual Handover
Date. Both cells have custom format 'ddd dd/mm/yy' However when MS handover
date is entered(eg user enters 13/12/06), it is displayed as 13/12/06 not Wed
13/12/06 as I was hoping for. Then to really mess it up, the Actual handover
date swaps the day and month around.

I know this is something to do with American and English display(Americans
have mm/dd/yy, English have dd/mm/yy) but the cell still shows the format as
ddd dd/mm/yy. I can only suppose it is something to do with VB working in
american, but why does it not affect any other cells,and how can i correct
it, more to the point.

Cheers

 

[NickHK]

If you use Application.InputBox instead the VBA one used here, there is an
argument of Type.
I don't have Help installed here, but I think one of the options is a date
type. Check the Help.

Also, as a matter of course, I would check that the user did not Cancel any
of the InputBoxes and decide what you should do in that case.

NickHK

 

[Kari J Keinonen]

That code works me. I have Office 2003 (USA Multilanguage Fi)

Private Sub CommandButton1_Click()
Dim MyName As String ' name type string
Dim MSHandoverDate As Date ' MSHdate type Date
Dim ActHandoverDate As Date ' ..
Dim DemoDate As Date ' ..
Dim TestDate As Date ' ..

Sheets("Milestone").Select
Range("C3").Select
Message = "Enter Customer Name..." ' Set prompt.
MyName = InputBox(Message, Default)
ActiveCell = MyName

Range("C4").Select
Message = "Please Enter MS Handover Date..." ' Set prompt.
MSHandoverDate = InputBox(Message, Default)
ActiveCell = MSHandoverDate

Range("C5").Select
Message = "Please Enter Actual Handover Date..." ' Set prompt.
ActHandoverDate = InputBox(Message, Default)
ActiveCell = ActHandoverDate

Range("C6").Select
Message = "Please Enter Demo Date..." ' Set prompt.
DemoDate = InputBox(Message, Default)
ActiveCell = DemoDate

Range("C7").Select
Message = "Please Enter Test & Adjust Date..." ' Set prompt.
TestDate = InputBox(Message, Default)
ActiveCell = TestDate

Range("C4:C7").Select
Selection.NumberFormat = "ddd dd/mm/yy"

End Sub

 

[DaveyJones]

Thanks both of you for that. I think I've got it sorted.