PDA

View Full Version : [SOLVED] Listbox default tem when the list is made up of dates



hairyfingers
10-15-2014, 02:42 AM
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
10-15-2014, 07:02 AM
Private Sub UserForm_Initialize()
With ListBox1
.RowSource = "a1:a3"
.TopIndex = 2
End With
End Sub

hairyfingers
10-15-2014, 07:58 AM
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
10-15-2014, 08:29 AM
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
10-15-2014, 03:03 PM
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