Help to incorporate a message in case someone selects more than X number of cells

[pablofu]

Hi there
I have a document where people can select some fields or not. I want to limit the number of selected fields (ticks) to 9. Could it be possible to modify the existing VBA macro to incorporate that limit and to trigger a message like "you have selected more than 9 fields. Please review and make sure that total number is below 9 or 9, not more"
I have attached the file for your review: Tool v6.xlsm The VBA project is embed in the Sheet1 (Matrix) tab.

Thanks a ton for your help!!

[AlphaFrog]

Put something like this at the top of the macro.

Please Login or Register  to view this content.

[pablofu]

Works great!!!! Thanks so much. Just for me to understand, what is the ChrW(10004) part?
Thanks again

[AlphaFrog]

Works great!!!! Thanks so much. Just for me to understand, what is the ChrW(10004) part?
Thanks again

You're welcome.

It's your checkmark character.

[pablofu]

Hi there
Your previous line code works wonders. Could you please help me with this other thing?, is it possible to display a message in case someone ticks a cell (i.e.: cells I3, I5, I7, etc.) but forgets to select which battle is it relevant for? (i.e.: cells J3, J5, J7).
At the end, if someone click on I3 he/she needs to tell me for which battle is it relevant for.
Message could be something like "Please review your selections and introduce relevant battles after competencies"
I've attached an example file

Example2.xlsx


THANKS A TON for your amazing help

[AlphaFrog]

At the end, if someone click on I3 he/she needs to tell me for which battle is it relevant for.

At the end of what? When do you want to trigger the message; what event? When the user checks I3, you cannot trigger the message then because they have not had a chance to select the battle.

Why have both a check mark selection and a battle selection? Maybe just have a battle selection and a formula in I3 that fills in a checkmark when any battle is selected in J3
I3
=IF(J3="-","","check_mark")

Note: the actual check_mark character doesn't display in this forum.

[pablofu]

Hey AlphaFrog,
I cannot use that formula since i need to specify for which battle people select any given competence. Each competence might be applicable for different battles.

Regarding your suggestion for the previous question:

Private Sub Worksheet_Change(ByVal Target As Range)

If Application.WorksheetFunction.CountIf(Range("F:I"), ChrW(10004)) > 9 Then
MsgBox "Please review and make sure the total number is less than or equal to 9. Not more. ", _
vbExclamation, "Selected More Than Nine Fields"
End If

I would like to first check a value, let say the value of cell A1, if the value is 1 then check the range as above and trigger a message if delection is above a certain number (should be no more than 4), if A1=2 the value shouldn't be above 8, etc.
Can you help with that?

Very much appreciated