Delete from named range based on Combo Box selection

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

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" <shebert.1tatql_1123257947.8484@excelforum-nospam.com> wrote in
message news:shebert.1tatql_1123257947.8484@excelforum-nospam.com...
>
> 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
>

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" <shebert.1tg86n_1123509982.9112@excelforum-nospam.com> wrote in
message news:shebert.1tg86n_1123509982.9112@excelforum-nospam.com...
>
> 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 if it would be easier to pull the named range into a combo box (as it is now) or just a basic drop down in excel for the task I am trying to accomplish?

Thanks again,

Seth