ListBox Remove Certain Text

[Darrell Lankford]

Does anyone know a code to fill a ListBox so that the cells with the
specific text don’t show up in the ListBox? Below is the basic code
that enters a list of students in the list box with their ID# and I
want the ones in Column "B" to not show in the ListBox that are
identified as "Prospect". Any help would be greatly appreciated.

Code
ListBox1.ColumnCount = 3
ListBox1.RowSource = "Stu_Data_List"


Worksheet
Column B Column C Column D
ID# Last Name First Name
SM001 Smith John (Show in Listbox)
JO001 Jones Kelly (Show in Listbox)
Prospect Bailey Bill (Not Show in Listbox)

 

[Per Jessen]

HI

Try this:

Private Sub UserForm_Initialize()
Dim ListRng As Range
ListBox1.ColumnCount = 3
For Each cell In Range("Stu_Data_List")
If cell.Column = 2 And cell <> "Prospect" Then
If ListRng Is Nothing Then
Set ListRng = Range("Stu_Data_List").Rows(1)
Else
Set ListRng = Union(ListRng,
Range("Stu_Data_List").Rows(cell.Row))
End If
End If
Next
ListBox1.RowSource = ListRng.Parent.Name & "!" & ListRng.Address
End Sub

Regards,
Per

 

[Darrell Lankford]

HI

Try this:

Private Sub UserForm_Initialize()
Dim ListRng As Range
ListBox1.ColumnCount = 3
For Each cell In Range("Stu_Data_List")
    If cell.Column = 2 And cell <> "Prospect" Then
        If ListRng Is Nothing Then
            Set ListRng = Range("Stu_Data_List").Rows(1)
        Else
            Set ListRng = Union(ListRng,
Range("Stu_Data_List").Rows(cell.Row))
        End If
    End If
Next
ListBox1.RowSource = ListRng.Parent.Name & "!" & ListRng.Address
End Sub

Regards,
Per





- Show quoted text -

Code as is gives me Runtime Error 380 "Could not set the rowsource
property. Invalid property value." I tried changing my ranges and
changing up the code a little and I get Runtime Error 91 "Object
variable or with block variable not set."

 

[Darrell Lankford]

Code as is gives me Runtime Error 380 "Could not set the rowsource
property. Invalid property value." I tried changing my ranges and
changing up the code a little and I get Runtime Error 91 "Object
variable or with block variable not set."- Hide quoted text -

- Show quoted text -

I tried naming the Range in Column B, C, & D to "Prospect_Find" and
using this code and it works, but it lists all the names in the
Listbox like:

SM001
Smith
John

Instead of:

SM001 Smith John



Dim Rng As Range
With ListBox1
.ColumnCount = 3
For Each Rng In Range("Prospect_Find").Cells
If Rng.Text <> "Prospect" Then
.AddItem Rng.Text
End If
Next Rng
End With

 

[GS]

Darrell Lankford formulated on Saturday :

Code as is gives me Runtime Error 380 "Could not set the rowsource
property. Invalid property value." I tried changing my ranges and
changing up the code a little and I get Runtime Error 91 "Object
variable or with block variable not set."

Since you're specifying a value for the ColumnCount property (and thus
creating a multi-column listbox), when adding data for any column other
than the BoundColumn, you need to specify the column index for each
piece of data.

<air code>
Dim i As Long
For i = 0 To Listbox1.ListCount - 1
With Listbox1
.List(i, 1) = "Smith"
.List(i, 2) = "John"
End With
Next

Where i is the first item (ie: "SM001" already entered) and '1' is
the index of the 2nd column, '2' is the index of the 3rd column, with
the 1st column (index '0') being the BoundColumn.

****************
Another approach
****************
Copy only the wanted data to a temp worksheet, load the range into a
variant, then set the .List property by passing the ref to the range.

<air code>
'Transfer range to the listbox
Dim tRange As Range, x As Variant
Set tRange = wksTemp.Range("A1:E1").CurrentRegion
x = tRange
frmSheetsList.lstCusSht.list = x
tRange = ""

HTH

 

[Darrell Lankford]

Darrell Lankford formulated on Saturday :







Since you're specifying a value for the ColumnCount property (and thus
creating a multi-column listbox), when adding data for any column other
than the BoundColumn, you need to specify the column index for each
piece of data.

  <air code>
  Dim i As Long
  For i = 0 To Listbox1.ListCount - 1
    With Listbox1
      .List(i, 1) = "Smith"
      .List(i, 2) = "John"
    End With
  Next

  Where i is the first item (ie: "SM001" already entered) and '1' is
the index of the 2nd column, '2' is the index of the 3rd column, with
the 1st column (index '0') being the BoundColumn.

****************
Another approach
****************
Copy only the wanted data to a temp worksheet, load the range into a
variant, then set the .List property by passing the ref to the range.

  <air code>
  'Transfer range to the listbox
  Dim tRange As Range, x As Variant
  Set tRange = wksTemp.Range("A1:E1").CurrentRegion
  x = tRange
  frmSheetsList.lstCusSht.list = x
  tRange = ""

HTH

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

I couldn't get any of the code to work like I wanted it, so I decided
to keep it simple. Put all the Students in a worksheet & the Prospects
on a separate worksheet that way only the Students show in the
ListBox. Thanks for the help thou.

Darrell

 

[GS]

Darrell Lankford formulated the question :

I couldn't get any of the code to work like I wanted it, so I decided

Did you change the range address to suit?
Did you declare the var 'wksTemp' as a worksheet?

to keep it simple. Put all the Students in a worksheet & the Prospects
on a separate worksheet that way only the Students show in the
ListBox. Thanks for the help thou.

Darrell

Well, that's basically what the intent of the code was: -to put the
student data on a temp sheet and load that into the Listbox. The code
is an excerpt from a working app that uses a multi-column listbox that
displays data from 5 separate sources (ergo the use of a temp
worksheet) This would allow you to store all the data on the same
worksheet. In my app, wksTemp remains hidden and is deleted after the
Listbox is filled.