Help with simple VBA to lock cells depending on conditions

[Nel H]

Hello,

I have been going through old threads and cant adapt the code to a very simple and particular need, perhaps have been trying to hard.

Its a 3 part questionnaire. Located in Cells D11-D13. the idea is to have D12 and D13 locked and unlocked only if D11 is "No", to which point you proceed to D12 and if "yes" lock the final cell. If no. Continue to last cell.

its simple I know... but i am getting frustrated.

 

[NARAYANK991]

Hi ,

Let me understand the logic better.

When the workbook is opened , the following will be the situation.

D11 - unlocked , D12 - locked , D13 - locked

The user has to select YES or NO in D11 , then YES or NO in D12 , and then YES or NO in D13.

At any stage , once the user makes a selection , the next cell in the sequence is unlocked. If the user's selection was YES , that cell is locked , else it remains unlocked.

Can you clarify or confirm ?

Narayan

 

[Nel H]

Hi ,

Let me understand the logic better.

When the workbook is opened , the following will be the situation.

D11 - unlocked , D12 - locked , D13 - locked

The user has to select YES or NO in D11 , then YES or NO in D12 , and then YES or NO in D13.

At any stage , once the user makes a selection , the next cell in the sequence is unlocked. If the user's selection was YES , that cell is locked , else it remains unlocked.

Can you clarify or confirm ?

Narayan

Hello Narayan. I appreciate the time you took to reply... The situation is as you mention. There can only be one Yes in either of the 3 cells and we don't want people to have the liberty to play around as they like, hence the locking. That being said is there a better way? Maybe when selecting a yes, regardless of the cell in the range the other two will automatically lock? Would that be easier?

D11 - "Yes" , D12 - locked , D13 - locked

Or

D11 - locked , D12 - "Yes" , D13 - locked

Or

D11 - locked , D12 - locked , D13 - "Yes"

 

[NARAYANK991]

Hi ,

The main point is whether the user knows that only one Yes is possible in the 3 cells.

If this is the only requirement , then we can see if we can have Data Validation so that when ever there is more than one YES entered in the 3 cells , an error message is displayed.

The advantage of this approach is that none of the 3 cells needs to be locked or unlocked ; all 3 cells are always available to the user for data entry , and a NO can be changed to a YES in any of them at any point in time ; the only constraint will be that if a cell has already been selected YES , and if the user wishes to select YES in some other cell , the cell which has already been selected YES will have to be changed to NO before another cell can be selected YES.

See the attached file.

Narayan

 

[Nel H]

Hi ,

The main point is whether the user knows that only one Yes is possible in the 3 cells.

If this is the only requirement , then we can see if we can have Data Validation so that when ever there is more than one YES entered in the 3 cells , an error message is displayed.

The advantage of this approach is that none of the 3 cells needs to be locked or unlocked ; all 3 cells are always available to the user for data entry , and a NO can be changed to a YES in any of them at any point in time ; the only constraint will be that if a cell has already been selected YES , and if the user wishes to select YES in some other cell , the cell which has already been selected YES will have to be changed to NO before another cell can be selected YES.

See the attached file.

Narayan

That could actually work. However, I can't use this method if i was intending on using a drop down list on these 3 cells or any of the other 6 questions... can I?

 

[NARAYANK991]

Hi ,

I haven't understood ; this Data Validation can be implemented across every block of 3 cells , even if there a hundred such blocks.

Narayan