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