Initializing values in a Listbox on a Form

[Marvin Buzz]

Assume cells a1:a3 the rowsource for LISTBOX1 contain
01/01/2008
02/01/2008
03/01/2008
I can code-
userform1.listbox1.value=range("a2").value
This wokks just fine.
If, however, I change the format of cells a1:a3 to a date format, the above
code will now fail. The failure also holds true if I use text instead of
value in the above line of code. The failure persists with any formatting
other than "General".
Any suggestions would be appreciated. I want to show my user a formatted
date, not a date serial number.
Thanks.

 

[Office_Novice]

UserForm1.ListBox1.RowSource = ("A1:A3")

 

[Dave Peterson]

If I want the second item in the list, then I'd use:

Me.ListBox1.List = Worksheets("sheet1").Range("a1:a10").Value
Me.ListBox1.ListIndex = 1

(0 is the first item, 1 is the second, ...)

 

[Marvin Buzz]

If I change the format of the data in sheet1, the listbox does not show the
data in the new format.

My objective is to have two listboxes, and populate the second box based on
the value of the first. For example, box 2 should always have a higher value
than box1. If that is not true, I want to place the next listed value from
box 1 into box 2. I then need both values so that I can use them as a filter
in another worksheet.

 

[Dave Peterson]

You can use .additem to add items to the listbox in the format you like. And
you can use the _change event for the first listbox to change the second listbox
entries.

I don't quite understand what you're doing, but this may help.

Option Explicit
Private Sub ListBox1_Change()

Dim iCtr As Long

Me.ListBox2.Clear

If Me.ListBox1.ListIndex < 0 Then
Exit Sub 'nothing selected
End If

With Me.ListBox1
For iCtr = .ListIndex + 1 To .ListCount - 1
Me.ListBox2.AddItem .List(iCtr)
Next iCtr
End With
End Sub
Private Sub UserForm_Initialize()

Dim myRng As Range
Dim myCell As Range

With Worksheets("sheet1")
Set myRng = .Range("a1:A10")
End With

For Each myCell In myRng.Cells
Me.ListBox1.AddItem Format(myCell.Value, "mmmm dd, yyyy")
Next myCell
End Sub