thought process

[Robert Couchman]

Hello all, and good morning.

can anyone please help, usually my programming is very
logical so when i break it down and program it, it works!

BUT...

at the moment, i need to try and figure out how to get a
form to update the correct data.

when a listbox is selected i need the contents of the
listbox to be a list of values from the spreadsheet that
contain a "" cell in column "G"

once this value has been selected, the after effect needs
to be that the row containing this value will put a "2" in
(.row, "G")

so if anyone can help, please do so!!

Thank you,

Robert Couchman
([email protected])

 

[Tom Ogilvy]

Are the values you are adding to the listbox unique - is there only one row
that contains that value. If so, then just loop through column G, looking
for "" and do an additem to the listbox. Then when a selection is made,
use the match function to find the offset into that range or loop through
the cells to find a match.

For each cell in Range("G1:G200")
if cell.Value = "" then
Listbox1.AddItem cell.offset(0,5).Value
end if
Next


Private Sub Listbox1_Click()
for each cell in Range("L1:L200")
if cell.Value = Listbox1.Value then
cell.offset(0,-5).Value = 2
end if
Next
End Sub

 

[Robert Couchman]

Hi Tom,

i wish it was that simple!!!

the only unique value in the row is the ID, but i do not
want this present in the listbox, then the other problem
is that when i loop through column G i find that it stops
when it gets to a cell with a value, then writes over the
list when it continues!

thank you,

Robert Couchman
([email protected])

 

[Tom Ogilvy]

It is that simple actually. You just use a two column listbox and make the
width of the second column 0. You put the address of the cell in the second
column.

I am not sure how you overwrite the list when you loop through cells in L if
you are changing G to 2.