Text box duplicate check

[heymur]

I have an excel user form I am working on to input golf course information. I cant figure out how to prevent the user from entering duplicate handicap information. there are 18 text boxes (1 per hole) and the need to have the values of 1 to 18 in no specific order, but can not be duplicates. I can get the values to be between 1 and 18, but I am unsure of how to prevent them from being duplicate values.

[6StringJazzer]

It would be helpful to see your file. It's not that it's too hard to do, but offhand I would say this is a design issue--why do you want to allow the user to enter the numbers in any order? I would just create 18 labels in order 1-18 rather than letting the user fill them in in unspecified order. It would ultimately be more intuitive for the user as well.

[heymur]

Hi Jeff, Thanks for your response. I do have 18 text boxes labelled 1-18 for the handicap rank entry for the 18 holes of a golf course. The problem is that each golf course ranks their hole difficulty differently so when I am entering the course data it may be 2, 4, 12, 14, 11, etc and not in order from 1 to 18. I just want to validate that no two holes end up with the same handicap rank or else it could change the net score on that hole when I enter the scoring data on another form.

[6StringJazzer]

Makes sense.

How have you named the text boxes? Are they Forms textboxes, or ActiveX text boxes? Are they on a UserForm, or on a worksheet? I can provide code with answers to these although it would be easier with your file, if you can attach it.

By the way, a completely different way to approach this is to present a list of the 18 holes, and provide a spinner control to let the user change the order of the holes in order of difficulty. That eliminates concern for duplicates. But I don't want to upset the applecart.

[heymur]

Here is the file, it is still a work in progress, and quite a mess. I am trying to figure out all the code to get it working before I clean it all up. The text boxes are on a user form.

[6StringJazzer]

I have rewritten CheckData as a function called GoodData that returns a Boolean to indicate whether the data is good. It adds a check for duplicate handicap rankings. It also streamlines checking for blank par. The same approach can be used in your other code. Please have a look.

However, it needs some more work that I did not do. You are checking for blank fields, but there are no checks for invalid data. You need to check that a handicap rank is an integer from 1-18. Par must be a positive number with some minimum and maximum. Same for yards.

[heymur]

Thanks for looking at this. The only problem I see is that i want to be able to leave Handicap ranks blank as it will be up to the user if he chooses to use them to figure out "net" scores and your code keeps telling me that all the holes are the same.

All of my other data is checked on exit from the text box, so the par values can only be between 3 and 5, and the slope can only be in "###" format and the rating can only be in "##.#" format and handicap numbers must be between 1 and 18, and the course name cannot exist on the worksheet already. this is my last hurdle before I can move on and it has been stumping me as I am very new to all of this code stuff!! I was hoping to do the handicap check on exit as well if that can happen.

[6StringJazzer]

Ah, I get it. OK, here is a revision. Another thing I did was to put all the error messages in a single MsgBox in case there are multiple errors on the same submit. Also, you may want to add another button to allow the user to cancel the operation gracefully.

[6StringJazzer]

I seem to be a step behind here. First, my exit validation requires all handicaps to be filled in, and you just said they can be left blank. So you can remove those lines. Second, you want to repeat the handicap check on exit. That is certainly possible but it would be redundant since they're being checked as they are filled in; not sure what the reasoning is there.

[heymur]

Thanks for all your help, I have it all working great now. I did remove the handicap check on exit, and I know I still need to add a cancel button. I want the handicap values to be an option for the user, it is not required for the calculations I am planning for now, but I may add a net score summary at some point where it would show you while you are entering your scores for a round what your net score on that hole would be as you enter it, but I will see how that goes at a later date. and I love the single message box, it is much neater then scrolling through 18 error messages if you forget to enter the par values!!

Thanks again, I know where to turn if I run into problems on the next couple forms I need to sort out!!

[6StringJazzer]

You're welcome, and thanks for marking your thread Solved! I'd be happy to take a look if you need further help.

[heymur]

Hi Jeff, my next step is creating a form that will look up and edit a course that is in my list should there be changes (which does happen). I have just started working on this form, but any tips you might have to help pull up the info from the worksheet "courses" after I select the course to update from a combo box would help!! this is something I am planning on picking away at hoping to have it ready for golf season next spring, and since most of this confuses me, it takes me a long time to get things put together!! I did change the layout of my add course form, and made one called edit course which looks exactly the same but I changed the course name text box to a combo box listing the names of courses on the courses worksheet, but that's about as far as I have gone so far.