MACRO or VBA if Yes selected then next cell active / if No Selected then next cell disabled

[dinkss]

Hi All,

What is the easiest way to make excel working in below way:
If I will select Yes from dropdown list then next cell to it will become active
If I will select No from dropdown menu then next cell to it will become disabled.

DATA	Number
Yes	If yes is answered this cell will be active
No	If no is answered this cell will be disabled

Is there any easy way? Please help, thank you.

 

[Joe4]

What is the exact range your drop-down boxes are in?
By "next to", do you mean immediately to the right or the left?

 

[dinkss]

What is the exact range your drop-down boxes are in?
By "next to", do you mean immediately to the right or the left?

Next to would mean to the right, to disable cell just beside it or two cells if needed.

So for e.g

Range of drop down selection is on:

=$G:$G
and
=$J:$J

 

[Joe4]

OK, you will need to do a few things to set it up.
First, you will need to unlock all the cells on the sheet, and then protect the sheet.
There are instructions on how to do this here: Lock or unlock specific areas of a protected worksheet - Microsoft Support.

Then, you can apply the following VBA code to do exactly what you want.
Note that in order for this to work correctly, it MUST be placed in the Sheet module (of the sheet you want to apply it to).
The easiest way to get there is to go to the sheet you want to apply it to, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in the resulting VB Editor window.

Private Sub Worksheet_Change(ByVal Target As Range)
    
'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub

'   See if change is made to column G or J
    If (Target.Column = 7) Or (Target.Column = 10) Then
'       See value entered in cell
        Select Case Target.Value
            Case "Yes"
'               Unlock cell to right
                ActiveSheet.Unprotect
                Target.Offset(0, 1).Locked = False
                ActiveSheet.Protect
            Case "No"
'               Lock cell to right
                ActiveSheet.Unprotect
                Target.Offset(0, 1).Locked = True
                ActiveSheet.Protect
        End Select
    End If

End Sub

This should automatically do what you want.

 

[dinkss]

Thank you very much, I will try that!

 

[dinkss]

I have this tested and once I select NO - it will lock the next cell but when I select Yes that cell stays locked. Did I do something wrong?

Also is it possible to have next 2 cells locked? If yes how can I change this?

 

[mannem_teja]

I have used this code and it works as intended. Note that the 'Yes' and 'No' are case sensitive.
To lock 2 cells next to the yes/no cell, you can use code below:

[/
Private Sub Worksheet_Change(ByVal Target As Range)

    
'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub

'   See if change is made to column G or J
    If (Target.Column = 7) Then
'       See value entered in cell
        Select Case Target.Value
            Case "Yes"
'               Unlock cell to right
                ActiveSheet.Unprotect
                Range(Target.Offset(0, 1), Target.Offset(0, 2)).Locked = False
                ActiveSheet.Protect
            Case "No"
'               Lock cell to right
                ActiveSheet.Unprotect
                Range(Target.Offset(0, 1), Target.Offset(0, 2)).Locked = True
                ActiveSheet.Protect
        End Select
    End If

End Sub

]

 

[Joe4]

I have this tested and once I select NO - it will lock the next cell but when I select Yes that cell stays locked. Did I do something wrong?

Also is it possible to have next 2 cells locked? If yes how can I change this?

To make my code NOT case-sensitive (so it works for "No", "NO", or "no", try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    
'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub

'   See if change is made to column G or J
    If (Target.Column = 7) Or (Target.Column = 10) Then
'       See value entered in cell
        Select Case UCase(Target.Value)
            Case "YES"
'               Unlock cell to right
                ActiveSheet.Unprotect
                Target.Offset(0, 1).Locked = False
                ActiveSheet.Protect
            Case "NO"
'               Lock cell to right
                ActiveSheet.Unprotect
                Target.Offset(0, 1).Locked = True
                ActiveSheet.Protect
        End Select
    End If

End Sub

 

[dinkss]

Perfect, thank you guys, will test it today