Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Initializing values in a Listbox on a Form
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Initializing values in a Listbox on a Form
UserForm1.ListBox1.RowSource = ("A1:A3")
"Marvin Buzz" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Initializing values in a Listbox on a Form
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 wrote: 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. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Initializing values in a Listbox on a Form
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" wrote: 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 wrote: 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. -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Initializing values in a Listbox on a Form
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 Marvin Buzz wrote: 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" wrote: 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 wrote: 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. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Initializing Form Controls | Excel Programming | |||
finding values and put them in a listbox on visual basic form | Excel Programming | |||
Initializing control values of a form | Excel Programming | |||
Fill values into a listbox matching selected values from a combobox | Excel Programming | |||
Initializing a Form with multiple forms embedded | Excel Programming |