ListBox Remove Certain Text

  • Thread starter Thread starter Darrell Lankford
  • Start date Start date
D

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)
 
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
 
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 -
Click to expand...



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."
 
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 -
Click to expand...




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
 
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."
Click to expand...

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 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 -
Click to expand...




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
 
Darrell Lankford formulated the question :
I couldn't get any of the code to work like I wanted it, so I decided
Click to expand...

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
Click to expand...

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