Listbox and Matched Enty

[Corey]

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....

 

[Ken]

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

 

[Corey]

Cheers

Corey....
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

 

[Dave Peterson]

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).