how to change excel datepicker format

[gomzi]

i am using excel 2007.i am unable to change the date format after selecting the date from date picker as shown in the image. i tried to change the format by right clicking the excel cell but still not working. i am not that expert in visual basic but still i can do if i get any suggestion. visual basic code written is as follows

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  With Sheet2.DTPicker1
    .Height = 20
    .Width = 20
    If Not Intersect(Target, Range("a13:l28")) Is Nothing Then
      .Visible = True
      .Top = Target.Top
      .Left = Target.Offset(0, 1).Left
      .LinkedCell = Target.Address
     Else
      .Visible = False
    End If
  End With
End Sub

 

[gomzi]

Please reply expert. Thanks

 

[mole999]

format the sheet cell as dd-mmm

 

[gomzi]

format the sheet cell as dd-mmm

Already done and checked but not changing. I think need to change through vba code

 

[mole999]

is the value number

 

[gomzi]

is the value number

I customed format the cell by right clicking as you mentioned dd-mmm but not working. Whereas without datepicker normal date its working. But date selection by datepicker not working. After selecting date by datepicker format is dd-mm-yyyy

 

[gomzi]

is the value number

Yes its working now but i observed that specified format not changing instantly after selection of date from datepicker. I need to click 2 times simultaneously on the particular cell and then if i click anywhere in excel sheet, desired format showing..