VBA Nested if statement to lock cells base on cell value

[SeliM]

I have an audit tool with a drop down selection of several service types. For two service types there are additional audit questions of varying sizes. I have been attempting to build script the provides for certain cells to be locked as they do not apply and for the two service types a range of cells to be locked when other service types are chosen.

For example "Community Nurse" and "Home Care Packages" have additional questions that do not apply to other services.
I have one script i found that helps on a new workbook:
Private Sub Worksheet_Change(ByVal Target As Range)
If [B3] = "Community Nurse" Then
[C40:C41].Locked = True
Else
[C40:C41].Locked = False
End If
End Sub

When I have use in my Workbook it does not work. I also cannot work through how to nest the If Statement to include "Home Care Packages."


Any guidance would be most appreciated.

Mel
ps I couldn't load the XL2bb system said the machine is in protect mode!

 

[Joe4]

Welcome to the Board!

Are you sure that you posted that code in the Sheet module of the sheet that you want to apply it to in VBA?
If you place it any other VBA module, it will not run automatically.
If you are not sure, go to the sheet that you want to apply it to, right-click on the sheet tab name at the bottom of the screen, select View Code, and post the VBA code in the VB Editor window that pops up there.

Also, you need to make sure that you have VBA enabled.

 

[Alex Blakenburg]

Locking or unlocking a cell will do nothing until you turn protection on.
See if this gives you any ideas.

Private Sub Worksheet_Change(ByVal Target As Range)

    ' Only perform the code if Cell B3 has been changed
    If Intersect(Target, Range("B3")) Is Nothing Then Exit Sub
    Me.Unprotect Password:="xxx"
    
    If [B3] = "Community Nurse" Then
        [C40:C41].Locked = True
    ElseIf [B3] = "Home Care Packages" Then
        [D40:D41].Locked = True
    Else
        [C40:C41].Locked = False
        [D40:D41].Locked = False
    End If
    
    Me.Protect Password:="xxx"
End Sub

 

[SeliM]

Many thanks
Most helpful

 

[Alex Blakenburg]

You're welcome. Glad we could help.