Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / November 2007

Tip: Looking for answers? Try searching our database.

check if values is already in listbox

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Frank M - 16 Nov 2007 10:39 GMT
hello,

i'm working with excel 2000 and i have the next code in my program:

Private Sub cmb1_Click()
 myRow = ActiveCell.Row
 Dim Bcell As Range
 Form1.ListBox1.Clear
 For Each Bcell In Range(("A" & myRow) & (":J" & myRow))
   If Bcell.Value = "C" Or Bcell.Value = "S" Or Bcell.Value = "I" Then
     Form1.ListBox1.AddItem
ThisWorkbook.Sheets("Sheet1").Range(((ConvertToLetter(Bcell.Column)) & "1"))
   End If
  Next Bcell
end sub

so basicly it does the following when myrow = 4
it searches for the values C, S or I in the range A4:J4. When one of these
letters is found the value of row 1 in that same column is added into the
listbox. But now i want a check that makes sure that if the value of row 1 is
already in the listbox, it is not added again. I know the code has to come
after If Bcell.Value = "C" Or ... and that it has to loop through the
listbox, but i have no idea what the code has to be. Can any help me with it?
paul.robinson@it-tallaght.ie - 16 Nov 2007 12:37 GMT
> hello,
>
[quoted text clipped - 19 lines]
> after If Bcell.Value = "C" Or ... and that it has to loop through the
> listbox, but i have no idea what the code has to be. Can any help me with it?

Hi
This is a classic problem we all come across eventually.
Add the items to a collection first, then add those to the list

Private Sub cmb1_Click()
  myRow = ActiveCell.Row
  Dim Bcell As Range
  Dim ListCollection as New Collection, ListValue as Variant, item as
Variant
  Form1.ListBox1.Clear
On error resume next
For Each Bcell In Range(("A" & myRow) & (":J" & myRow))
    If Bcell.Value = "C" Or Bcell.Value = "S" Or Bcell.Value = "I"
Then
        ListValue =
ThisWorkbook.Sheets("Sheet1").Range(((ConvertToLetter(Bcell.Column)) &
"1"))
        ListCollection.Add ListValue, Cstr(ListValue)
    End if
Next Bcell
on error goto 0
'now add to listbox
    for each Item in ListCollection
      Form1.ListBox1.AddItem Item
   next item
end sub

The add in a collection creates an error if the item already exists,
and the copy is not added. The "on error" bits catch the errors.
Check John Walkenbach's website for more on this (like sorting the
list) - I think this was his idea first.

regards
Paul
Frank M - 16 Nov 2007 13:04 GMT
Thanks, this works exactly how i want it

> > hello,
> >
[quoted text clipped - 54 lines]
> regards
> Paul
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.