Have a userform that puts team picks on a sheet.

There will be a different sheet for each player.

The pool we are playing does not allow you to pick a team
Twice in the season.

Is there a way to have some type of validation, that it would check prior teams and
Not allow input of a prior picked team.

From picture below, bears, 49'ers, texans, saints, and panthers could not be picked again.

http://i819.photobucket.com/albums/zz111/lacogada/NFLpicks.jpg

I tried this and get error highlight of "CountIf".


Private Sub Worksheet_Change(ByVal Target As Range)

Dim Cell As Range
For Each Cell In Range("C3:C17")
If Application.WorksheetFunction.CountIf(Cell.Value) = 1 Then MsgBox "Good Pick"
Else
MsgBox " Duplicate Pick - Choose Another Team"
End If
Next Cell


End Sub

A Worksheet_Change is triggered when something is entered on the sheet, so I would suggest:


Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("C3:C17")) Is Nothing Then
If Application.WorksheetFunction.CountIf(Range("C3:C17"), Target.Value) = 1 Then
MsgBox "Good Pick"
Else
MsgBox " Duplicate Pick - Choose Another Team"
Application.Undo
End If
End If

End Sub

The message boxes work as should.

Get error and "Application.Undo" is highlighted with message "Method Undo of object_Application Failed.

Any thoughts on another way besides "worksheet_change" event. I actually would not need the "Good Pick" message, just the "Duplicate Pick" message.

Thanks JBeaucaire

After my first post above, I had changed the userform by adding a "Reset" button to remove the last entry. (have that part working)

http://i819.photobucket.com/albums/zz111/lacogada/NFLpicks-1.jpg

The "application.undo" would be great but if it will not work they could just use the reset to remove the last entry.

The problem is whenever reset is hit the message box keeps popping up because of the worksheet_change event.

Thus my question about an alternative to worksheet_change event.

PS : tried the macro recorder to see what would show for "undo" from the menu and
Also using Ctrl-Z... but neither show up in the macro.

It was a stab in the dark. I would put all the "check" code into the form itself. So when you click on the ENTER button, the code should take the value of the selected element and search the sheet to see if it has been used, then respond appropriately if it has, else enter it on the sheet and clear the form.

If you REALLY wanted a slick form, I would be disabling elements as they are added to the sheet. Then this whole issue goes away.

So when you click on the ENTER button, the code should take the value of the selected element and search the sheet to see if it has been used, then respond appropriately if it has, else enter it on the sheet and clear the form.


Thought that's what your previous code did, whew... this stuff over my head.

I would welcome and try any of your idea's.

I'll just add that each player would have separate worksheet ( 10 to 15 ) and I like the userform to stay open while switching worksheets.

Thanks for your help.

I would recommend you actually close the form as you switch from sheet to sheet and have the form reopen in the new sheet, this way you can more easily cycle through all your elements and turn off the ones again that are on the activesheet already as you initialize your form, once again presenting the user only the teams that are available.

I would recommend you actually close the form as you switch from sheet to sheet and have the form reopen in the new sheet.

OK... I know how to have it close and reopen with each sheet, but would not know how to "cycle through the elements and turn off" the ones not needed.

Thanks

You know the names of your elements. You know the caption text of those elements.

You can cycle through the elements that are listed in column C and compare them to the captions and disable any that are used during the initialization of the form. Something long these lines:


Private Sub UserForm_Initialize()
Dim obj As Control

For Each obj In Me.Controls
If obj.Name Like "OptionButton*" Then
If WorksheetFunction.CountIf(Range("C:C"), obj.Caption) > 0 Then
obj.Enabled = False
Else
obj.Enabled = True
End If
End If
Next obj

Me.Show
End Sub

Use a similar technique to turn off elements individually as you insert them onto the sheet... or cheat. Put the new element on the sheet, then call the Initiailize macro again since that already does the enable/disable for you.

Tried installing to different sections, sheet code or userform code, but could not get it to work.

Sometimes form would not open, sometimes it would open with all teams greyed out.

Just do not know enough to set it up.

I'll attach file if you care to look at it.

Thanks for your efforts.

The main problem was that you were using Labels for the team names and then the OptionButton captions were simply unused. I deleted all the Labels and simply put the team names into the OptionButton captions, less controls to deal with and now you can very easily rearrange the teams to suit yourself.

I've made the form only show itself for sheets that have the word WEEK in A3. And it will disable any team names already used. It is still modeless. You can switch from sheet to sheet and the teams will update for the current activesheet.

The main problem was that you were using Labels for the team names and then the OptionButton captions were simply unused.

I see... the labels were really useless as the button have their own caption.

The file now works perfectly... if I enter wrong team and use the "Remove Last" button... it updates sheet and form instantly, that is awesome.

Thanks JBeaucaire