CODEWITHSUNDEEP
excelvba
user36510
user36510

Reputation: 91

Date-value missmatch after manual Input

Via Inputbox I want to define the date used in a cell. 

Sub Refresh()

Dim myValue As Variant

myValue = InputBox("Give me a starting date")
Workbooks.Open Filename:="Y:\ 3300.xls"
Range("A1").Value = myValue
ActiveWorkbook.Close SaveChanges:=True

End Sub

The weird thing is, that sometimes the given date is correct, some times reversed (checked by reopening the given file). I tried the following dates :

30/01/17 is correct
14/05/17 is correct
04/02/17 is reversed to 02/04/17
08/09/17 is reversed to 09/08/17
10/05/17 is reversed to 05/10/17
05/10/17 is reversed to 10/05/17

The cell format is according the above (dd/mm/yy) and the behaviour stays even if I change it and adapt the input accordingly

Upvotes: 0

Views: 28

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19722

Excel doesn't recognise your entry as a date. It's all just text entered to an inputbox and Excel tries to format it when you add it to the sheet. By adding CDATE it converts the entered value to a real date - I'm sure someone will correct me here if I'm wrong, but it appears to convert it using your local settings.

Another potential problem I see is that you're just looking at the ActiveWorkbook which may not be the one just opened. The code below adds the workbook to a variable so you can be sure you're always looking at the correct book and the correct sheet in that book.

Sub Refresh()

    Dim myValue As Date
    Dim wrkBk As Workbook

    myValue = InputBox("Give me a starting date")
    Set wrkBk = Workbooks.Open("Y:\ 3300.xls")
    wrkBk.Worksheets("Sheet1").Range("A1") = CDate(myValue)
    wrkBk.Close SaveChanges:=True

End Sub

Upvotes: 1

Related Questions