Data Validation of Multiple Criteria with VBA

[perpectuals]

Dear Friends.

Attached an excel sheet.

I Will input either "BCOM" or "MCOM" text in Cells of Column A. And then Input in Column B in years "2016" or "2017". If any of these values are there in cells of Columns A and Columns B respectively, then all other cells should be allowed to be editable. Else, It should make other cells in all other columns locked restricting the user not to input any data further.

I don't want in formulas or Lists or using Data Validation settings. I need it in VBA Codes.

Request you to help.

Thank you.

Regards.
Perpectuals

[playaller]

You'll need a worksheet change event


Right click on your worksheet and click "View Code" and paste the code below. Every time a Qualification or Passing Out Date changes, the sheet will dynamically check all data to lock and unlock cells. If you need to add a password then update the 4 lines of code ending with '"Password", removing the ' and changing "Password" to "Your Password".

Please Login or Register  to view this content.

[perpectuals]

Dear playaller.

First of all , thanks for your valuable time and effort. I had tired and applied your code. The code allows me...

1. To enter values in all other sheets even if column A contains values other than BCOM and MCOM as well as if column B contains values other than 2016 and 2017. Attached sheet is for your reference.

2. Also, when i add up values BCOM or MCOM in Cell A4 or A5, its prompting for the password.

3. I will enter the data/alter it in columns A and columns B. Based on it all other colums should be locked or unloacked, if A & B Columns doesnt contain any of those applicable values, which i defined. Hope i had clearly describing here.

Please change the code and provide.

Thanks.
Perpectuals

[playaller]

Sorry. I forgot to put the date in quotations and also needed to finish the loop to lock if second criteria not met. Update with the code below and save as a XLSM file.

Please Login or Register  to view this content.

[perpectuals]

Thanks playaller. Works fine. One change needs to be done.

For example:

I entered the below row:

A B Col1 ....

BCOM 2017 jfgdjf

If i alter this A or B next time to BBA or 2015, the other cells unlocked, which is very fine. But the column 1 dat which i had already given above is still there. Can u please completely erase it.

Also, if i want to make any modifications in years or course or delete one criteria/add one criteria, how can i do that in these codes. Bcoz i dont wanna disturb you often in this same issue.

Regards.
Perpectuals

[playaller]

I'm not sure what you mean by erase the column above? I need better explanation on what happens to erase it and what erases? Do you mean if you duplicate a qualification then the entire row of the previous qualification should be deleted? I need a very detailed example of what you mean.

I updated the code so you can add or remove Qualification criteria. I've created one line per qualification with one date per qualification. So if you want 2 years for one qualification then it's 2 lines of code. If you want 3 then copy it and add another line for that qualification. As you can see in the code there are 2 qualifications matched with 2 years so there are 2 lines of code for each qualification. Just repeat it however your like or create a new line and add a new qualification name and or years to add additional like so:

"If c = "NEWBCOM" And c.Offset(0, 1).Value = "2018" Then Range("C" & cr, Cells(cr, lc)).Cells.Locked = False"

Please Login or Register  to view this content.

[perpectuals]

Thanx Playaller for the assistance. Will check it and reply.

Regards.
Perpectuals

[perpectuals]

Thanks Playaller. It works. Thanks for your assistance.

Regards.
Perpectuals

[playaller]

Anytime. Glad I could help!