format date (Excel 2000)

[HUHandEH]

Hi all, I am tring to figure out how to format the date that is inputted using an access calendar control, what i have here works, but I would like to know how to change the format to be format(date,"ddmmyy").
Thank you all,
Darryl.

Private Sub CommandButton1_Click()
Dim x As Range
Set x = Application.Worksheets(1).Cells(1, 1)
x = Me.Calendar1.Value
End Sub

[HansV]

Since the cell is fixed, you don't need to set the format in code, you can do it interactively:
- Select the cell.
- Select Format | Cells...
- In the Number tab, select the Custom category.
- Enter ddmmyy in the Type box.
- Click OK.

[HUHandEH]

Good point Hans..
What if it wasn't fixed? What I would like to do is keep it fixed, but add a string at the beginning of the date, and a string at the end of the date.

Private Sub_cmbck

Application.Worksheets(2).Cells(2, 1).Value = TextBox1.Value & Calendar1.Value _
& TextBox3.Value

how do I format the value of calendar1 as format(date,dd,mm,yy)?


Thanks Darryl

[HansV]

Use the Format function in the code:

Application.Worksheets(2).Cells(2, 1).Value = TextBox1.Value & Format(Calendar1.Value, "ddmmyy") _
& TextBox3.Value

[HUHandEH]

Thanks Hans.