Decreasing Validation List - Excel 2003

[Robert]

Okay...I have a validation list set up to select names from a named range on
a different sheet. No problem there. What I’d like to do it is reduce the
selection of names from the list once a selection has been made; but only for
a selected column!! For example, if the list is of “names†and it has Tom,
Rob, and Fred in it, if the user selects Rob from the list, and if the list
is accessed again in the same column, Rob does not appear as an option to
select again. Alright.....I have a VB code that will automatically remove
the selected item from the drop-down validation list once it has been
selected once. However, the particular code I’m using only allows each name
in the range to be used only once anywhere that the list is called. If
actually removes the selected reference from the original named list
permanently! This is my problem; I do not want the original named list
deleted. I want the validation list of choices to reduce. The reason is
that I want to use the named list in other places, with additional validation
lists (again to select from the original list). I could simply make x-number
of hidden copies of my original named list but that seems counterproductive.
Is there a way to modify the VB code that it just temporarily reduces the
visible selections until a new column is selected?

I have uploaded a copy of a sample file from which am working
(http://campus.pc.edu/~rarts/validation_test.xls). Basically, on the results
page, I’d like to be able to select names from my list for a particular
column (Event #), switch columns (Events) and be able to pick from my
original list again until used up for that column, and etcetera....can anyone
help?

 

[Mike H.]

My suggestion would be to modify the selection list and build an array in
memory of the items that were "pulled". THen when the user changes columns,
rebuild the selection list "on the fly". You would need to use this function
to do this:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)

End Sub
which you would place in the "ThisWorkbook" area in VBA. You would need to
record what column you are in when you load the spreadsheet so you can then
be able to identify when the column changes. This doesn't sound too
difficult to me, but if you need help doing this let me know.

You would need to create a public variable to record the column using the
Private Sub Workbook_Activate()

End Sub
sub that would be placed in the same area. Then in the Change sub
(displayed above), have code that would change the data in the selection
range. Good luck!

 

[Robert]

Thanks Mike...I'll give it a shot!

Robert.