How do I create a pop up message if a certain value appears more than 15 times in a column range

[Stunasmearse]

Ask Question

Ask Question

up vote1down votefavorite ​

I thought this would be an easy one, but I must be overlooking something and I have had no luck searching. I have a basic shared workbook for a sports day sign up. In column B the members select which sport they wish to play. I am trying to create a pop up message to appear for when the number of people enter "Soccer" in column B exceed 15. The column range is between B5:B200. I have the following but its not working. As always any help is greatly appreciated. <code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;"> </code>Private Sub Worksheet_Change(ByVal Target As Range)<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Set Target = Me.Range("B5:B200") If Target.Value = "SOCCER" > 15 Then MsgBox "You have exceeded the 15 Players limit, please select another sport" End If End Sub </code>

<tbody style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; font-size: inherit; line-height: inherit; font-family: inherit; vertical-align: baseline;">
</tbody>

 

[Scott T]

Try

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("B5:B200")) Is Nothing Then
     If Application.CountIf(Range("B5:B200"), "SOCCER") > 15 Then MsgBox "You have exceeded the 15 Players limit, please select another sport"
End If


End Sub

 

[Fluff]

Crossposted https://stackoverflow.com/questions...a-certain-value-appears-more-than-15-times-i#

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Stunasmearse]

Try

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("B5:B200")) Is Nothing Then
     If Application.CountIf(Range("B5:B200"), "SOCCER") > 15 Then MsgBox "You have exceeded the 15 Players limit, please select another sport"
End If


End Sub

Thanks very much, it is perfect!

 

[Stunasmearse]

Crossposted https://stackoverflow.com/questions...a-certain-value-appears-more-than-15-times-i#

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

My apologies (still learning)...great site , appreciated all the help..