Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
check if values is already in listbox
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(((ConvertToLet ter(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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
check if values is already in listbox
On Nov 16, 10:39 am, Frank M wrote:
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(((ConvertToLet ter(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? 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(((ConvertToLet ter(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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
check if values is already in listbox
Thanks, this works exactly how i want it
" wrote: On Nov 16, 10:39 am, Frank M wrote: 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(((ConvertToLet ter(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? 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(((ConvertToLet ter(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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop through column(s) to check values, perform action based on check | Excel Programming | |||
Multiselect listbox with check boxes | Excel Programming | |||
Fill values into a listbox matching selected values from a combobox | Excel Programming | |||
Compare Listbox values with Collection values | Excel Programming | |||
Sorting ListBox results or transposing ListBox values to other cells for sorting | Excel Programming |