Check multiple cells and give message if multiple are selected

[Kumarkusa]

Hi,

We have a sheet where users can select Yes/No drop downs in 5 cells.....They are able to select multiple cells.....BUt it should be ONLY 1 Yes selection...If they select multiple cells as Yes...and try to submit data.. then the data submission should be stopped.

We need to check the cells..and if multiple cells are selected as 'Yes'..then we need to show a message ..and stop data submission...

All these cells have named ranges..how can we put this in Code.

Thanks

 

[SirJB7]

Hi, Kumarkusa!

Welcome to Chandoo forums!
Checked this yet?
http://chandoo.org/forum/forums/new-users-please-start-here.14/

About your question there are many ways to skin a cat:
a) Data validation
b) VBA code at "submit data" process
c) Replacing the 5 drop down menus of Yes/No by 5 checkboxes and VBA code
With which one would you feel more comfortable?

Regards!

 

[NARAYANK991]

Hi ,

You can use a helper cell where you have a formula such as :

=COUNTIF(datarange , "Yes")

Your existing code which runs when the SUBMIT button is pressed , can check to see if this is more than 1 , and if so display a message alerting the user , and exit the routine which does the data submission.

If you can post the existing code , or even better upload your complete workbook , the necessary changes can be implemented within your workbook.

Narayan