• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA to check if certain mandatory cells are filled [SOLVED]

inddon

Member
Hello There,

I would like to do the following:

There are certain mandatory cells marked with * and need to be filled. There is a button. On click it should check if the cells with * signs have a value corresponding to its data type.
example First name (characters only), Date of birth (date), Age (Numbers only).

If any mandatory cells are left empty then it's border should be red colour and a message is displayed on a certain column ("Some mandatory cells need to be filled") in red. If all mandatory cells are filled it should activate worksheet "Sheet2".

I have attached a sample workbook for your reference.

Could you please help to achieve this via VBA?

Looking forward to hearing from you

Thanks & regards
Don
 

Attachments

  • Mandatory cells.xlsm
    16.3 KB · Views: 12
How I'd propose doing this:
Select all the mandatory cells. Give these cells a name, e.g. "MyRange"
Use a cell somewhere with this formula:
=COUNTA(MyRange)=5
where the 5 is = to number of cells.
Then your VB only has to check this single cell. If True, all filled in. Otherwise, at least 1 is missing.
 
I'd also suggest using Data validation on the input cells if the rules for the cells are known
 
How I'd propose doing this:
Select all the mandatory cells. Give these cells a name, e.g. "MyRange"
Use a cell somewhere with this formula:
=COUNTA(MyRange)=5
where the 5 is = to number of cells.
Then your VB only has to check this single cell. If True, all filled in. Otherwise, at least 1 is missing.


Hi Luke,

Thank you for your advise.

I followed your code and it works as expected (attached workbook). Could you please guide how to border the cell which is empty with red color?. Also to check if the data type for the mandatory cells is entered correctly (eg. character, date, number)


Thanks & regards
Don
 

Attachments

  • Mandatory cells.xlsm
    20.6 KB · Views: 33
Select your Mandatory Cells. Apply Conditional Format, based on Cell Value equal to:
=""
Format as Red.

For the Date Cell, to go Data - Data Validation (DV), choose Date. If desired, limit the input range.
For Age, do DV again, but this time pick whole number, with Minimum of 0. I'd suggest a max of something like 125
For Name, DV again, this time pick Custom, and use this formula:
=ISTEXT(F6)
 
Select your Mandatory Cells. Apply Conditional Format, based on Cell Value equal to:
=""
Format as Red.

For the Date Cell, to go Data - Data Validation (DV), choose Date. If desired, limit the input range.
For Age, do DV again, but this time pick whole number, with Minimum of 0. I'd suggest a max of something like 125
For Name, DV again, this time pick Custom, and use this formula:
=ISTEXT(F6)


Thank you Luke for your advise. All works well

Could you please update the Post subject to [SOLVED]

Regards
Don
 
Back
Top