Visual Basic Help - Need N/A or Drop Down List to Populate - Based on Data in Another Cell

[lomo0208]

Cell B8 can be blank, Yes or No.
When B8 is Yes, I need my data validation list to appear in fields G28:G35. This is simple and I have my listed named Status.
When B9 is No, I need N/A to automatically populate in fields G28:G35. I also need these cells locked.
I found some VBA help topics but I cannot get it to work for me.

I am not sure if I need to add my data validation first or second or what to key in Visual Basic. I will be repeating this same scenario at least 15 times with different cells.

 

[onlyadrafter]

Hello,

something on the lines of

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$8" Then
        Select Case Target
            Case "Yes"
                Range("G28:G35").Select
                Range("G28:G35").Locked = False
                With Selection.Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                        xlBetween, Formula1:="=IF($B$8=""Yes"",INDIRECT(""Status""))"
                    .IgnoreBlank = True
                    .InCellDropdown = True
                    .InputTitle = ""
                    .ErrorTitle = ""
                    .InputMessage = ""
                    .ErrorMessage = ""
                    .ShowInput = True
                    .ShowError = True
                 End With
            Case "No"
                Range("G28:G35").Locked = True
                For Each cell In Range("G28:G35")
                    cell.Value = "N/A"
                Next cell
            Case Empty
                Range("G28:G35").Locked = False
                For Each cell In Range("G28:G35")
                    cell.ClearContents
                Next cell
        End Select
    End If
End Sub

this code needs to go into the relevant sheet code window, not a standard module.

 

[lomo0208]

Beautiful! This works great. I am hoping you can help me with two more things.

I decided to move cell B8 to another sheet because I am going to have a large number of yes/no questions. The cell is now on a sheet called Account Summary. I tried to update the code and reference this sheet, but apparently I do not know what I am doing.

If someone incorrectly enters "No" in B8 and deletes "No", the N/As in the range will disappear. If they change "No" to "Yes" in B8, the N/As remain in the range. Is it possible to update the code to indicate if "Yes" is selected, the cells will clear and only have the option of the drop down list?