Don't format the cell. Convert date to a string and write the string to the
cell

Private Sub Calendar1_Click()
ActiveCell.Value = Now()

ActiveCell = Format(ActiveCell, "dd/mm/yy")
ActiveCell.Select
End Sub

"Hazel" wrote:

Hi Everybody

I have added a calendar to a userform and used Ron DB's code to get it
working e.g

Private Sub Calendar1_Click()
ActiveCell.Value = CDbl(Calendar1.Value)

ActiveCell.NumberFormat = "dd/mm/yy"
ActiveCell.Select
End Sub

My problem is the date enters the ActiveCell OK (UK DATE) 01/07/07 no
problem and in a Listbox on viewing the row it reads as 01/07/07 however on
highlighting the Listbox Row in the TextBox assigned to the date it is the
serial number. Have tried Value - Text - formatting everyway but which all to
no avail. If I enter the date manually in the Cell and use Text as the format
in the Textbox everything is Ok. Any idea's to help solve this would be much
appreciated.
--
Many thanks

hazel