Listbox default tem when the list is made up of dates

[hairyfingers]

Hi

This is doing my head in!

if I create a simple userform and insert a listbox then on the initilize code enter:

Private Sub UserForm_Initialize()
With ListBox1
.RowSource = "a1:a3"
.Value = ListBox1.List(2)
End With
End Sub

then in cell a1:a3 enter the letters A, B, and C respectively

the form works.

however if I change cells a1:a3 to 31/3/14, 31/3/15, 31/3/16 (three dates in the UK format) then the form does not work. it falls apart on the .value property.


if I delete the .value property the form intiailizes but for some reason with it in it cannot identify the item at position 3 in the list to be the default selection.

RARR!!!!

why is this????

[SamT]

Private Sub UserForm_Initialize()
With ListBox1
.RowSource = "a1:a3"
.TopIndex = 2
End With
End Sub

[hairyfingers]

Private Sub UserForm_Initialize()
With ListBox1
.RowSource = "a1:a3"
.TopIndex = 2
End With
End Sub

thanks for responding.

I need the list index number to be selected by default at the initialze point rather than just available for selection at the top ((the index number will vary dependant upon different variables).

any other insights? is it because its a list of dates that breaks it, because if I format the dates to the underlying number it works fine.

[hairyfingers]

It's OK I've solved it.

I needed

.Value = (Format(Range(x), "DD/MM/YYYY"))

where x matched the row address I wanted.

It seems although the listbox displays the dates it is actually looking at the underlying number - so by formatting the number to the date format it worked.

cheers

[SamT]

I need the list index number to be selected by default at the initialze point

Sorry.

Private Sub UserForm_Initialize() 
    With ListBox1 
        .RowSource = "a1:a3" 
        .Selected(2) = True
    End With 
End Sub