Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox and Matched Enty
I have a listbox popultaed from the U/F Activate event as below:
Private Sub UserForm_Activate() Application.ScreenUpdating = False Dim LastCell As Long Dim myrow As Long LastCell = Worksheets("Data").Cells(Rows.Count, "A").End(xlUp).Row With ActiveWorkbook.Worksheets("Data") ..Select 'first thing to do with a With statement that occurs on a second sheet For myrow = 1 To LastCell If .Cells(myrow, 1) < "" Then If .Cells(myrow, 1) < "" Then ListBox1.AddItem Cells(myrow, 1) End If End If Next End With Image1.Visible = True Application.ScreenUpdating = True ListBox1.ListIndex = 0 End Sub BUT, I want to have the Listbox(or textbox) to have NO value in it UNTIL an Entry is placed into it. I have set the Match Entry to Complete, as i want the user to enter the value and if there is a completed matched entry then this will display. How can i do this? Corey.... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox and Matched Enty
Corey
I think listboxes necessarily show the list. It sounds like you would be better off with a combobox, with the listindex=-1. Private Sub UserForm_Activate() Application.ScreenUpdating = False Dim LastCell As Long Dim myrow As Long LastCell = Worksheets("Data").Cells(Rows.Count, "A").End(xlUp).Row With ActiveWorkbook.Worksheets("Data") For myrow = 1 To LastCell If .Cells(myrow, 1) < "" Then If .Cells(myrow, 1) < "" Then ComboBox1.AddItem Cells(myrow, 1) End If End If Next End With Image1.Visible = True Application.ScreenUpdating = True ComboBox1.ListIndex = -1 End Sub Good luck. Ken Norfolk, Va On Mar 19, 5:20 pm, "Corey" wrote: I have a listbox popultaed from the U/F Activate event as below: Private Sub UserForm_Activate() Application.ScreenUpdating = False Dim LastCell As Long Dim myrow As Long LastCell = Worksheets("Data").Cells(Rows.Count, "A").End(xlUp).Row With ActiveWorkbook.Worksheets("Data") .Select 'first thing to do with a With statement that occurs on a second sheet For myrow = 1 To LastCell If .Cells(myrow, 1) < "" Then If .Cells(myrow, 1) < "" Then ListBox1.AddItem Cells(myrow, 1) End If End If Next End With Image1.Visible = True Application.ScreenUpdating = True ListBox1.ListIndex = 0 End Sub BUT, I want to have the Listbox(or textbox) to have NO value in it UNTIL an Entry is placed into it. I have set the Match Entry to Complete, as i want the user to enter the value and if there is a completed matched entry then this will display. How can i do this? Corey.... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox and Matched Enty
Cheers
Corey.... "Ken" wrote in message oups.com... Corey I think listboxes necessarily show the list. It sounds like you would be better off with a combobox, with the listindex=-1. Private Sub UserForm_Activate() Application.ScreenUpdating = False Dim LastCell As Long Dim myrow As Long LastCell = Worksheets("Data").Cells(Rows.Count, "A").End(xlUp).Row With ActiveWorkbook.Worksheets("Data") For myrow = 1 To LastCell If .Cells(myrow, 1) < "" Then If .Cells(myrow, 1) < "" Then ComboBox1.AddItem Cells(myrow, 1) End If End If Next End With Image1.Visible = True Application.ScreenUpdating = True ComboBox1.ListIndex = -1 End Sub Good luck. Ken Norfolk, Va On Mar 19, 5:20 pm, "Corey" wrote: I have a listbox popultaed from the U/F Activate event as below: Private Sub UserForm_Activate() Application.ScreenUpdating = False Dim LastCell As Long Dim myrow As Long LastCell = Worksheets("Data").Cells(Rows.Count, "A").End(xlUp).Row With ActiveWorkbook.Worksheets("Data") .Select 'first thing to do with a With statement that occurs on a second sheet For myrow = 1 To LastCell If .Cells(myrow, 1) < "" Then If .Cells(myrow, 1) < "" Then ListBox1.AddItem Cells(myrow, 1) End If End If Next End With Image1.Visible = True Application.ScreenUpdating = True ListBox1.ListIndex = 0 End Sub BUT, I want to have the Listbox(or textbox) to have NO value in it UNTIL an Entry is placed into it. I have set the Match Entry to Complete, as i want the user to enter the value and if there is a completed matched entry then this will display. How can i do this? Corey.... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox and Matched Enty
First, you don't have to select that Data worksheet. But you do have to qualify
the range on this line: ListBox1.AddItem Cells(myrow, 1) change it to: ListBox1.AddItem .Cells(myrow, 1) Second, this line says to select the top entry in the listbox: ListBox1.ListIndex = 0 You can delete it (easiest) or change it to: ListBox1.ListIndex = -1 (the -1 says unselect everything--might be useful later). Corey wrote: I have a listbox popultaed from the U/F Activate event as below: Private Sub UserForm_Activate() Application.ScreenUpdating = False Dim LastCell As Long Dim myrow As Long LastCell = Worksheets("Data").Cells(Rows.Count, "A").End(xlUp).Row With ActiveWorkbook.Worksheets("Data") .Select 'first thing to do with a With statement that occurs on a second sheet For myrow = 1 To LastCell If .Cells(myrow, 1) < "" Then If .Cells(myrow, 1) < "" Then ListBox1.AddItem Cells(myrow, 1) End If End If Next End With Image1.Visible = True Application.ScreenUpdating = True ListBox1.ListIndex = 0 End Sub BUT, I want to have the Listbox(or textbox) to have NO value in it UNTIL an Entry is placed into it. I have set the Match Entry to Complete, as i want the user to enter the value and if there is a completed matched entry then this will display. How can i do this? Corey.... -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Which criteria was matched? | Excel Discussion (Misc queries) | |||
Return matched value 2 criteria | Excel Worksheet Functions | |||
How to count the matched values? | Excel Discussion (Misc queries) | |||
Help with Getting Totals of Matched Numbers Please | Excel Programming | |||
listbox.value not equal to listbox.list(listbox.listindex,0) | Excel Programming |