Remove item from Validation list after selection (Excel XP [2002] Win XP S

Topic

New Reply

Excel Experts:
I have been busy searching the lounge and while I have found some great information on validation lists, I have not found guidance as to my particular issue. My workbook has 95 sheets, one for each scheduled baseball game. What I would like to do, is have a validation list with 12 players name in it to start each sheet [alphabetical order by last name [i.e., family name]. As a player is selected, I would like to remove that name from the list for that sheet only. Once the starting nine players have been selected the remaining three names in the list are the substitutes. The last 3 names will selected in the substitute name section of each worksheet.

My current application has the same validation list available for each worksheet and does NOT remove names as they are selected on the current worksheet. If I could remove a name once it is selected, the data entry would go much faster but I am very poor with VBA [at least starting it from scratch] and I imagine this is the only solution to remove items from the list once selected.

I am also struggling with how to “reset” the list to 12 players so that when one goes to the second sheet [second game], and so on for all future games, that all 12 players are again available in the list to select. Is it possible to create one data validation list that is available to all 95 sheets and then have code on each sheet to remove names as selected?

Of course, I haven’t thought about how to handle the coach that makes a mistake and needs a name to reappear in the list because the player was selected in error on that sheet. But if I get this far it will be a world of improvement over the current approach. Thanks for your time to consider my questions….take care.

Reply | Quote

Replies

I wouldn’t use validation for this, I fear it would be difficult to maintain.

Is the order in which the players are selected important?

Reply | Quote

Hans,
The player names are selected in batting order [ 1 through 9] from the validation list. While the batting order generally stays consistent, variation does occur especially if one of the normal substitutes is in the starting batting order. Even among the normal starting 9 players, the batting order can change slightly if someone is not hitting well and someone else is hitting well.

Given this variability, I created the current validation list in order by player last name–so the coach could quickly locate the player on the list. The idea to a remove a selected player from the list, reduces the list size and quickens the selection process as player names are selected into the batting order. It also elminates the error that a coach could select the same player twice [this happens more frequently than I would like].

I hope this additional information is helpful…thanks for your consideration.

Reply | Quote

Steve’s approach is probably the simplest, but see the attached workbook for an approach using a userform with two list boxes. Click the command button to show the form.

Note: the workbook contains VBA code, so you must enable macros, otherwise the command buttons wont work.

Reply | Quote

Hans,
I think this will work!! While I admitted in my other post that I don’t understand Steve’s approach totally, his seems to require double “work” by the coaches—I am afraid they won’t understand why they need to do such a closely related task twice.

All coaches are required to have EXCEL, but your point about macro security is a good one as I will need to also provide guidance about how to accept macros, etc. I do have question on this front, say I customize and forward your worksheet—if the user doesn’t permit macro code will the worksheet still open, so that they could view a page with instructions on how to permit macro code to execute?

I also printed out your code….wow! No make that a double wow!! I am trying to learn VBA with every post I make in the Lounge…so I most likely will be back for an explanation or two after I try to digest your code. Thank you very much!!

Reply | Quote

If the user disables macros (either because Macro security is set to High, or because it is set to Medium and the user clicks “Disable macros” in the prompt that appears), the workbook will still open, but clicking the command buttons won’t do anything. You could put instructions on a worksheet (either in cells, or in a text box from the Drawing toolbar).

Reply | Quote

Hans,
Thanks for the clarification….and, as always, thank you for your commitment to teach others about Excel and other MS Software through Woody’s lounge. Again, THANKS!!!

Reply | Quote

Why not just have a list a the names and place the numbers 1-9 next to the names. You could keep the alphabetized list and create a new one for the lineup.

You could use validation to make sure numbers were not entered more than once.

A simple example is attached. Just add the numbers 1-9 within A1- A12. The first 3 blanks will be sub1-3

Steve

Reply | Quote

Steve,
Thanks for the concept. I am little unsure about the user friendliness of this approch, but I want to make sure that I understand it correctly. In the yellow block [column A], the coach would assign a number one through nine to each starting player. Any player without a number 1 through 9 assigned in column A is a “substitute”. The coach then goes to column D, and types a number 1 through 9 and the corresponding last name is filled in the adjacent row in column F. Am I correct?

If so, my concern is that this approach will take the coach longer with this approach than using a full validation list to select from as currently employed. Additionally, the players have jersey numbers [these are often the boy’s favorite number]. These jersey numbers stay fixed during the season and the twleve player numbers range from 0 [zero] to 56. I guess I need to think about a methodology that may use the assigned player jersey number in your lookup example.

Am I missing something? It looks like to me the coach must enter data twice on your worksheet, yes? Thanks for your consideration and proposed solution. Take care.

Reply | Quote

The coach only has to enter the numbers 1-9 in col A. The rest is automatic. The coach does not have to go to D, this is already entered in since this will not change. The material in D/E is just the lineup and the lineup will always be in order…

In addition to looking up the names, the numbers could be looked up as well…

You could insert a column of jersey numbers next to the names and use a similar lookup formula to lookup the number.

A/B© would have names, numbers etc.

You could even (if desired) have 1 sheet with just player into (names, number, etc and a column for the order for each game and have all the sheets read the appropriate order from this sheet and then extract any of the info into that sheet.

Steve

Reply | Quote

Steve,
Thanks for the additional information. After reading your explanation, the version I downloaded didn’t work like your explanation. I re-down loaded the file and now it works as explained. At least I don’t feel quite so dense on this topic….Thanks for your patience.

Now that I have your approach working and understand it better, I will need to really think about the best approach as I may face a distribution issue with Hans solution should the coaches have their EXCEL settings to prohibit macro code. What I am finding more and more, is that Excel settings have either been modified by the PC owner or a 3rd party to prohibit macro code. These users also believe that macro code is only used for “bad” things and it is a challenge to educate them to permit macro execution when the worksheet is from a “trusted” source. Given that all the coaches are volunteers from all walks of life, I may not be able to implement a macro based solution.

Thanks again for your time and consideration. Take care.

Reply | Quote

I purposely tried to make a “formula-approach” which required no macros. They are generally easier to maintain and don’t have the “macro security” issues.

Either Hans or my approach can be further adapted to whatever you may need or want. Post back with questions and/or comments about whichever approach you choose and we can try and help you further…

Steve

Reply | Quote

Steve,
Thanks again for your assistance. I may need some more help in the process to take your idea into the application I am trying to develop…the coaches would welcome such an enhancement…Since my last post I sent a demo sheet to only 5 of 42 coaches. Four of these five, already have either the macro setting to “high” or “medium”. The 2 individuals at medium “prefer to not authorize” macro code to run on their home computer. So I most likely will follow your approach to avoid this issue.

Hans’ approach gave me another idea to ease some significant work I do for the baseball league [boys aged 12-14]. His suggestion may save me a great deal of time as the “volunteer” statistics manager. Again, thank you both for your contributions to the Excel forum!! Again, thank you.

Reply | Quote