VBA double click event - derive date

[sh1pley]

Hi I am trying to write some code for a double click event. I have a sheet called "Calendar" that has all the dates in the year.

Cell A1 has the year
The months go across and the number dates go down.

In cell B7 there is the number 6 which corresponds to 06/01/2011

In the sheet "Diary", cell L2 I need to derive this date. I have used the following code which works if the number in the activecell is higher than 12 but swaps the days and months over if the value is between 01 and 12. I guess this has something to do with settings but is there a work around?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim CellCol As Integer
CellCol = ActiveCell.Column - 1

Sheets("Diary").Range("L2") = ActiveCell.Value & "/" & CellCol & "/" & Sheets("Calendar").Range("A1")

End Sub

 

[Andrew Poulsom]

Try:

Sheets("Diary").Range("L2").Value = DateValue(ActiveCell.Value & "/" & CellCol & "/" & Sheets("Calendar").Range("A1").Value)

 

[sh1pley]

Perfect - Thanks