[X]My Assistant

[davideyles]

Hi All
I've recorded and tweaked a simple macro to clear a form and the get various input boxes to re- enter new data. I am however struggling with the date input part. Even though my regional settings are UK, the date is entered in the format mm/dd/yy. How can I fix this??


David




Sub Clear_and_renter()
'
' Clear_and_renter Macro
'

'
Application.ScreenUpdating = False
Range("D5:D7").Select
Selection.ClearContents
Range("D9").Select
Selection.ClearContents
Range("F8").Select
Selection.ClearContents
Range("M3:M4").Select
Selection.ClearContents

Range("M3").Select
ActiveCell.FormulaR1C1 = InputBox("Enter Fresh Sample Hours", Default:=48)
Range("M4").Select
ActiveCell.FormulaR1C1 = InputBox("Enter Frozen Sample Hours", Default:=54)
Range("D5").Select
ActiveCell.FormulaR1C1 = InputBox("Enter Client Name")
Range("D6").Select
ActiveCell.FormulaR1C1 = InputBox("Enter Number of Donors")
Range("D7").Select
ActiveCell.FormulaR1C1 = InputBox("Enter Breed")
Range("D9").Select
ActiveCell.FormulaR1C1 = InputBox("Enter Flushing and Transfer Date", Default:="dd/mm/yy")
Range("F8").Select
ActiveCell.FormulaR1C1 = InputBox("Enter Treatment Time", Default:="hh:mm")
Application.ScreenUpdating = True

End Sub

[arnelgp]

you format the cells using Custom format: dd/mm/yy

[davideyles]

Hi
The cell is formatted to dd/mm/yy, but when I enter the date 05/09/19 (or even 5/9/19) it enters into the cell 09/05/19 which is mm/dd/yy!!


David

[arnelgp]

inputbox returns string.
after inputting the date on the box, parse the string
and convert it to proper date:

CODE

Dim sDate As String
Dim var As Variant
sDate = InputBox("Enter Flushing and Transfer Date", Default:="dd/mm/yy")
'check if date has entered
If IsDate(sDate) Then
   var = Split(sDate, "/")
   ActiveCell.FormulaR1C1 = DateSerial(Val(var(2)), Val(var(1)), Val(var(0)))
End If

This post has been edited by arnelgp: Sep 9 2019, 04:35 AM