Combobox Listitems date value not displayed properly

[arihan1511]

With Sheets("OPD")
    Set rVisibles = .Range("A2", .Cells(Rows.Count, "A").End(xlUp)).SpecialCells(12)
End With
For Each rCell In rVisibles
    rCell.Value = Format(rCell.Value, "dd/mm/yyyy hh:mm AM/PM")
    Me.DateAdded.AddItem rCell.Value
   

Next rCell

I have this Database which contains Date as "dd/mm/yyyy hh:mm AM/PM" format in Column A.After Auto filtering the data, visible cells in column A is added as list items to combobox named dateadded which has change event based on date value.Column A and dateadded both are formatted to "dd/mm/yyyy hh:mm AM/PM" and date is displayed properly in same format.
Its working fine till here

Now issue is when visible cell in column has figures in double digit example 15/11/2015 10.07 AM it is displayed same in dropdown list

But when date is 05/06/2015 01.05 AM in visible cell in column A it is displayed as 5/6/2015 1.05 AM in List item hence combobox change event is not triggered because of value mismatch

I want visible cell values to be displayed in list items in same format "dd/mm/yyyy hh:mm AM/PM"
Any Help to resolve the issue will be highly appreciated

 

[dourpil]

Hi arihan,

I'm not sure it'll solve your issue but I've had trouble with date formatting in Excel VBA. I solved them in most of my files by formatting the cell prior to filling it with my date (but I had trouble displaying the correct format FROM a userform TO a cell). Trying to format my value itself is not working very often.

Perhaps:
rCell.NumberFormat = "dd/mm/yyyy hh:mm AM/PM"
Me.DateAdded.AddItem rCell

?? Although in this case I don't think it'd do the trick since we changed the format of the cell.

Or perhaps try formatting your combobox? Something like
Me.DateAdded = Format(Me.DateAdded, "dd/mm/yyyy hh:mm AM/PM")
?

 

[arihan1511]

Sorry for late reply
This code is still not working
I have found a method by copy paste visible cells to another sheet and name that range
Then set rowsource property to named range