ListBox Remove Certain Text
 

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)

ListBox Remove Certain Text
 

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

On 26 Nov., 17:06, Darrell Lankford wrote:
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)

ListBox Remove Certain Text
 

On Nov 27, 4:20*am, Per Jessen wrote:
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

On 26 Nov., 17:06, Darrell Lankford wrote:



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)- Hide quoted text -

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

ListBox Remove Certain Text
 

On Nov 27, 11:53*am, Darrell Lankford wrote:
On Nov 27, 4:20*am, Per Jessen wrote:





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

On 26 Nov., 17:06, Darrell Lankford wrote:

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)- Hide quoted text -

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

ListBox Remove Certain Text
 

Darrell Lankford formulated on Saturday :
On Nov 27, 4:20Â*am, Per Jessen wrote:
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

On 26 Nov., 17:06, Darrell Lankford wrote:



Does anyone know a code to fill a ListBox so that the cells with the
specific text dont 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)- Hide quoted
text -

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

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 at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

ListBox Remove Certain Text
 

On Nov 27, 4:29*pm, GS wrote:
Darrell Lankford formulated on Saturday :





On Nov 27, 4:20*am, Per Jessen wrote:
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

On 26 Nov., 17:06, Darrell Lankford wrote:

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)- Hide quoted
text -

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

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

ListBox Remove Certain Text
 

Darrell Lankford formulated the question :
On Nov 27, 4:29Â*pm, GS wrote:
Darrell Lankford formulated on Saturday :





On Nov 27, 4:20Â*am, Per Jessen wrote:
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

On 26 Nov., 17:06, Darrell Lankford wrote:
Does anyone know a code to fill a ListBox so that the cells with the
specific text dont 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)- Hide quoted
text -

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

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

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.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc