Formatting dates in a combobox dropdown list

I have created a simple userform with a combobox populated with a range of dates (rngWeekList) but I am having serious headaches trying to get the list in the dropdown box to appear in "dd-mmm-yy" format. Here is my code:

Private Sub UserForm_Initialize()

    ' Populate the list with the date range
    ComboBox1.List = Worksheets("Cover").Range("rngWeekList").Value

    ' Set the defulat selection (based off rngWeekIndex)
    ComboBox1.ListIndex = Worksheets("Cover").Range("rngWeekIndex").Value - 1

    ' Format
    ComboBox1 = Format(ComboBox1, "dd-mmm-yy")

End Sub

Private Sub ComboBox1_Change()
    ' Format 
    ComboBox1 = Format(ComboBox1, "dd-mmm-yy")
End Sub

This manages to format the selected item in the combobox correctly (e.g. "02-Jul-14") but when I open the dropdown list, all the list entries shown are formatted in the default "m/d/yyyy". Is there a way to change the formatting for the list entries? It is confusing for users who are used to seeing the day before the month.

Thanks in advance for your help, it is much appreciated.

Ed

Answers


I managed to fix it by looping through each item in the comboboax and formatting it (feel free to correct me if there is a more elegant way to do it!)

Private Sub UserForm_Initialize()
Dim i As Integer

    ' Populate the list with the date range
    ComboBox1.List = Worksheets("Cover").Range("rngWeekList").Value

    'Format all items
    For i = 0 To ComboBox1.ListCount - 1
        ComboBox1.List(i) = Format(DateValue(ComboBox1.List(i)), "dd-mmm-yy")
    Next i

    ' Set the default selection (based off rngWeekIndex)
    ComboBox1.ListIndex = Worksheets("Cover").Range("rngWeekIndex").Value - 1

End Sub

Private Sub ComboBox1_Change()
    ' Format the selection
    ComboBox1 = Format(ComboBox1, "dd-mmm-yy")
End Sub

Sorry for posting, but I really thought I was stuck.

Thanks again,

Ed


Need Your Help

Return Dictionary value if key substring exist

c# list dictionary substring

I want to return the value from a dictionary if the key contains a string which half matches string lookup.

How to force the x-axis tick marks to appear at the end of bar in heatmap graph?

r ggplot2

I created a simple heatmap graph with ggplot2 but I need to force the x-axis tick marks to appear at the end of my x variable, rather than at its center. For example, I would expect 1 to appear at...