I have a named range on worksheet 1 (parts). That named range populate
a combo box on worksheet 3 by using a vlookup. Once a selection is mad
via the combo box, I want it to delete the exact entry from the name
range (hence deleting it from the combo box as well). I can delet
sequential rows based on a selection, but the combo box selections wil
not always be in order. The 1:1 match is the issue here.

Anyone done something like this already. Any help or hints would b
greatly appreciated.

Thanks,

Set

--
sheber
-----------------------------------------------------------------------
shebert's Profile: http://www.excelforum.com/member.php...fo&userid=2596
View this thread: http://www.excelforum.com/showthread.php?threadid=39333

If your using a vlookup, this would imply that the value returned is not
necessarily unique.

that said, you can search through the values returned and find the one that
matches the value in the combobox.

there are various ways to search. Loop, use FIND, use Application.Match.

--
Regards,
Tom Ogilvy

"shebert" wrote in
message ...

I have a named range on worksheet 1 (parts). That named range populates
a combo box on worksheet 3 by using a vlookup. Once a selection is made
via the combo box, I want it to delete the exact entry from the named
range (hence deleting it from the combo box as well). I can delete
sequential rows based on a selection, but the combo box selections will
not always be in order. The 1:1 match is the issue here.

Anyone done something like this already. Any help or hints would be
greatly appreciated.

Thanks,

Seth


--
shebert
------------------------------------------------------------------------
shebert's Profile:
http://www.excelforum.com/member.php...o&userid=25965
View this thread: http://www.excelforum.com/showthread...hreadid=393335

Maybe my original statement was a little unclear (sorry for the mix-up)


I have an excel 2000 workbook that has two worksheets. On worksheet(1
I have a named range (a list of parts). On worksheet(2) I have a comb
box that is populated from the named range on worksheet(1) by
vlookup. This combo box is listed several hundred times o
worksheet(2). Once a selection is made in the combo box, I would lik
to have it removed from the named range(worksheet1), hence removing i
from the combo box(worksheet2). Each selection can only be made once.

I have tried to record a macro to delete each part from the named rang
(worksheet1), but it will only delete in sequential order from the firs
selection (it is not intellegent). I have not programmed in a goo
while, so I was trying to work around any VBA, etc. However, I thin
that I may need some code to accomplish this task. Has anyone els
accomplished a task similar to this one? Can anyone shed some light o
this subject for me?

Thanks for your time,

Set

--
sheber
-----------------------------------------------------------------------
shebert's Profile: http://www.excelforum.com/member.php...fo&userid=2596
View this thread: http://www.excelforum.com/showthread.php?threadid=39333

Public bBlockEvents as boolean

Private Sub Combobox1_click()
If Combobox1.ListIndex = -1 then exit sub
If bBlockEvents = True then exit sub
bBlockEvents = True
set rng = Range(Combobox1.RowSource)
set rng1 = rng.columns(1)
set rng2 = rng1(combobox1.ListIndex + 1)
Combobox1.RowSource = ""
rng2.EntireRow.Delete
set rng = rng.Resize(rows.count-1)
combobox1.RowSource = rng.Address(external:=True)
bBlockEvents = False
end Sub

would be a basic outline, but this would clear the selection in the combobox
and likewise in any linked cell. So there are things to play with here. I
suspect it could recursively call the click event - I have successfully
implemented similar in the past and recall it to be a bit screwy , but not
recently and I didn't play with this to check. If that doesn't work, try
putting similar code in the keydown event.

--
Regards,
Tom Ogilvy



"shebert" wrote in
message ...

Maybe my original statement was a little unclear (sorry for the mix-up).


I have an excel 2000 workbook that has two worksheets. On worksheet(1)
I have a named range (a list of parts). On worksheet(2) I have a combo
box that is populated from the named range on worksheet(1) by a
vlookup. This combo box is listed several hundred times on
worksheet(2). Once a selection is made in the combo box, I would like
to have it removed from the named range(worksheet1), hence removing it
from the combo box(worksheet2). Each selection can only be made once.

I have tried to record a macro to delete each part from the named range
(worksheet1), but it will only delete in sequential order from the first
selection (it is not intellegent). I have not programmed in a good
while, so I was trying to work around any VBA, etc. However, I think
that I may need some code to accomplish this task. Has anyone else
accomplished a task similar to this one? Can anyone shed some light on
this subject for me?

Thanks for your time,

Seth


--
shebert
------------------------------------------------------------------------
shebert's Profile:
http://www.excelforum.com/member.php...o&userid=25965
View this thread: http://www.excelforum.com/showthread...hreadid=393335

Thanks for the reply. I will try it this evening. I was wondering i
it would be easier to pull the named range into a combo box (as it i
now) or just a basic drop down in excel for the task I am trying t
accomplish?

Thanks again,

Set

--
sheber
-----------------------------------------------------------------------
shebert's Profile: http://www.excelforum.com/member.php...fo&userid=2596
View this thread: http://www.excelforum.com/showthread.php?threadid=39333