Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Decreasing Validation List - Excel 2003
Okay...I have a validation list set up to select names from a named range on
a different sheet. No problem there. What Id 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 Im 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, Id 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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Decreasing Validation List - Excel 2003
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Decreasing Validation List - Excel 2003
Thanks Mike...I'll give it a shot!
Robert. "Mike H." wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003. Data/Validation/Settings - Allow/List: sizing list? | Excel Discussion (Misc queries) | |||
Excel 2003-Data Validation drop down list not working? | Excel Worksheet Functions | |||
data validation when list is on a different worksheet? 2003 | Excel Discussion (Misc queries) | |||
Make Validation List Wider - Excel 2003 | Excel Discussion (Misc queries) | |||
Decreasing number of Values in Pivot Tables Value List | Excel Discussion (Misc queries) |