ActiveX Listbox filled with date. But converts to serial numbers

[Junarkar]

Hai

I have inserted an Active X list box. Populated it with dates of current month. Linked it to a cell also. But when i select a date from this ListBox, the linked cell shows some serial number instead of selected date. Even if I format that cell to date, still it is not changing to date.

I tried combobox active X control which works just fine. Even form controls also. But I want to use ListBox Active X control due to its size and aesthetic appeal.

 

[chirayu]

sample file please

 

[Junarkar]

Hai Chirayu

Uploading the sample file.

Thanks in Advance.

 

[Haz]

I just inserted a ListBox and changed the format in A1 to mmm/dd, it worked fine
It seems the problem is the ComboBox, not the opposite

 

[Belleke]

Hi,
use this code

With ComboBox1
    .List = Sheets("Week").Range("S1:S" & Sheets("Week").Cells(Rows.Count, 19).End(xlUp).Row).Value
        For i = 0 To .ListCount - 1
            .List(i, 0) = Format(.List(i, 0), "mmm/dd")
        Next i
End With

(In the example i put the code in the Worksheet_Activate() event)
This also works for a ListBox.
See attached

 

[Junarkar]

Thanks

I just inserted a ListBox and changed the format in A1 to mmm/dd, it worked fine
It seems the problem is the ComboBox, not the opposite

Haz. Don't know why it is not working in my file.

 

[Junarkar]

Hi,
use this code

With ComboBox1
    .List = Sheets("Week").Range("S1:S" & Sheets("Week").Cells(Rows.Count, 19).End(xlUp).Row).Value
        For i = 0 To .ListCount - 1
            .List(i, 0) = Format(.List(i, 0), "mmm/dd")
        Next i
End With

(In the example i put the code in the Worksheet_Activate() event)
This also works for a ListBox.
See attached

New
Thanks alot Belleke . A little more favour , Can you help me with codes which fill the list box with dates of the current month as well.

 

[Belleke]

Something like that perhaps?
see attached