How to combine multiple "worksheet_change events" in a protected worksheet? VBA

Question

I have a code (code 1) to protect some cells from being edited, dependent on another cell. I also have another piece of code (code 2 ). This code is removes values next to dropdownlist when I change it. They are both based on worksheet_change subs.

Now I want to combine the two (code 3). Yet when I do this the code 1 functionallity doesn't work anymore. It does not give me an error, but it also no longer locks content cell. Does anyone know what I need to change to combine them so they both work?

Code 1

Private Sub Worksheet_Change(ByVal Target As Range)
    
If Range("B2") = "Text 1" Then
    Sheet2.Unprotect Password:="Secret"
        Range("F4:Q4").Locked = True
        Range("B2").Locked = False
        Sheet2.Protect Password:="Secret"
 ElseIf Range("B2") = "text 2" Then
 Sheet2.Unprotect Password:="Secret"
         Range("F4:I4").Locked = False
         Range("B2").Locked = False
        Range("J4:Q4").Locked = True
        Sheet2.Protect Password:="Secret"
    ElseIf Range("B2") = "text 3" Then
    Sheet2.Unprotect Password:="Secret"
        Range("B4:Q4").Locked = False
        Range("B2").Locked = False
        Sheet2.Protect Password:="Secret"
    ElseIf Range("B2") = "text 4" Then
    Sheet2.Unprotect Password:="Secret"
        Range("B4:Q4").Locked = False
        Range("B2").Locked = False
        Sheet2.Protect Password:="Secret"
    End If

Code 2

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
  If Target.Validation.Type = 3 Then
   Application.EnableEvents = False
   Target.Offset(0, 1).ClearContents
  End If
End If

exitHandler:
  Application.EnableEvents = True
  Exit Sub
End Sub

Code 3

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = True
If Target.Column = 2 Then
  If Target.Validation.Type = 3 Then
  Sheet2.Unprotect Password:="Secret"
   Application.EnableEvents = False
   Target.Offset(0, 1).ClearContents
   Sheet2.Protect Password:="Secret"
  End If
End If

exitHandler:
  Application.EnableEvents = True
  Exit Sub

If Range("B2") = "C-shuttle 150 core" Then
    Sheet2.Unprotect Password:="Secret"
        Range("F4:Q4").Locked = True
        Range("B2").Locked = False
        Sheet2.Protect Password:="Secret"
 ElseIf Range("B2") = "C-shuttle 250 core" Then
 Sheet2.Unprotect Password:="Secret"
         Range("F4:I4").Locked = False
         Range("B2").Locked = False
        Range("J4:Q4").Locked = True
        Sheet2.Protect Password:="Secret"
    ElseIf Range("B2") = "C-shuttle 250 core with platfrom for DB or TD" Then
    Sheet2.Unprotect Password:="Secret"
        Range("B4:Q4").Locked = False
        Range("B2").Locked = False
        Sheet2.Protect Password:="Secret"
    ElseIf Range("B2") = "C-shuttle 350 core" Then
    Sheet2.Unprotect Password:="Secret"
        Range("B4:Q4").Locked = False
        Range("B2").Locked = False
        Sheet2.Protect Password:="Secret"
    
    End If





End Sub

Update:

I changed code 3, i found it is important to embed

Sheet2.Unprotect Password:="Secret"

in if clause. I edited code 3.

Is that new `Worksheet_Change` in the worksheet that contains the cells next to the dropdown lists, or in `Sheet2`? — GSerg, Feb 12 '21 at 12:27
All the code is about the same worksheet, so in this case Sheet2 — Josse_, Feb 12 '21 at 12:36

score 1 · Accepted Answer · answered Feb 12 '21 at 17:56

Reason Code 2 does not run without errors is because you have an Exit Sub directive just before the code block. VBA line labels with colon do not prevent code from running. They will be processed in order. Usually these should be placed at end of subroutines as part of error handling.

Consider the following adjustment with error handling and use of more readable Select Case instead of ElseIf. Also, consider explicit context using With...End With and period qualifiers. (Avoid unqualified Range anywhere in Excel code. See How to avoid using Select in Excel VBA). Finally, handle .Unprotect and .Protect once:

Private Sub Worksheet_Change(ByVal Target As Range)

    Sheet2.Unprotect Password:="Secret"    ' UNPROTECT SHEET WHEN ENTERING SUB

    Application.EnableEvents = True
    If Target.Column = 2 and Target.Validation.Type = 3 Then
        Application.EnableEvents = False
        Target.Offset(0, 1).ClearContents
    End If

    With Sheet2
        Select Case .Range("B2") 
            Case "C-shuttle 150 core"        
                .Range("F4:Q4").Locked = True
                .Range("B2").Locked = False

            Case "C-shuttle 250 core"
                .Range("F4:I4").Locked = False
                .Range("B2").Locked = False
                .Range("J4:Q4").Locked = True

            Case "C-shuttle 250 core with platfrom for DB or TD"
                .Range("B4:Q4").Locked = False
                .Range("B2").Locked = False
        
            Case "C-shuttle 350 core"
                .Range("B4:Q4").Locked = False
                .Range("B2").Locked = False        
        End Select
    End With

ExitHandler:
    Sheet2.Protect Password:="Secret"      ' ALWAYS PROTECT SHEET AT END ERROR OR NOT
    Application.EnableEvents = True
    Exit Sub

ErrHandler:
    MsgBox Err.Number & " - " & Err.Description, vbCritical
    Resume ExitHandler
End Sub

Thanks a lot! That completly solved my problem and gave me more insights in how to work with VBA. Thanks! — Josse_, Feb 15 '21 at 11:18

How to combine multiple "worksheet_change events" in a protected worksheet? VBA

1 Answers1