VBA Lock Cells - Error Type 13 Mismatch - Multiple Cells

[jacoblewis1994]

Hi,

I have a Yes / No data validation list in column A. When Yes is selected I want rows B - T to be locked and want it unlocked when it is empty / no is selected.

The below code works brilliant but only when one cell is inputted with Yes. When multiple cells in column A are inserted with Yes (e.g. copy and paste) then the Type 13 Mismatch error comes up.

Private Sub WorkSheet_Change(ByVal Target As Range)
If Target.Column > 1 Then Exit Sub
ActiveSheet.Unprotect
    Select Case Target.Value
        Case "Yes"
             With Target.EntireRow
                .Locked = True
                .FormulaHidden = True
             End With
        Case Else:
             With Target.EntireRow
                .Locked = False
                .FormulaHidden = False
             End With
    End Select
    With Target.EntireColumn
        .Locked = False
        .FormulaHidden = False
    End With
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

When I hit debug the following bit of code is highlighted:

Case "Yes"

Any help would be appreciated.

Thanks

 

[Sixthsense]

Try this...

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] WorkSheet_Change([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)

[COLOR=darkblue]If[/COLOR] Target.Column > 1 [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
[COLOR=#ff0000]If Target.Cells.Count > 1 Then Exit Sub[/COLOR]

ActiveSheet.Unprotect

[COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] Target.Value
    [COLOR=darkblue]With[/COLOR] Target.EntireRow
    [COLOR=darkblue]Case[/COLOR] "Yes"
         [COLOR=darkblue]With[/COLOR] Target.EntireRow
            .Locked = [COLOR=darkblue]True[/COLOR]
            .FormulaHidden = [COLOR=darkblue]True[/COLOR]
         [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    [COLOR=darkblue]Case[/COLOR] Else:
         [COLOR=darkblue]With[/COLOR] Target.EntireRow
            .Locked = [COLOR=darkblue]False[/COLOR]
            .FormulaHidden = [COLOR=darkblue]False[/COLOR]
         [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]

[COLOR=darkblue]With[/COLOR] Target.EntireColumn
    .Locked = [COLOR=darkblue]False[/COLOR]
    .FormulaHidden = [COLOR=darkblue]False[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[jacoblewis1994]

I now get this error:

"Compile error: Statements and labels invalid between Select Case and first Case"

 

[Andrew Poulsom]

Remove the With statement between Select Case and first Case.

 

[jacoblewis1994]

That's brilliant thanks - it seems to work.

Only problem is that I have 5000 rows of "No" in column A yet there are all protected.... When I go through one by one and change them to Yes and then back to No they are unprotected but this doesn't work if I copy No all the way down to the bottom...

 

[jacoblewis1994]

I have just tried in new worksheet where I deleted all the No's and when I enter the first Yes it protects all the rows. Also when I enter No to unprotect and then copy and paste Yes over a series of rows it does not protect them

 

[Sixthsense]

this doesn't work if I copy No all the way down to the bottom...

Replace your current code with the below one and check

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] WorkSheet_Change([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Range)

[color=darkblue]If[/color] Intersect(Range("A:A"), Target) [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
[color=darkblue]Dim[/color] r [color=darkblue]As[/color] Range

Application.ScreenUpdating = [color=darkblue]False[/color]

Me.Unprotect

[color=darkblue]For[/color] [color=darkblue]Each[/color] r [color=darkblue]In[/color] Intersect(Range("A:A"), Target).Cells
    [color=darkblue]Select[/color] [color=darkblue]Case[/color] r.Value
        [color=darkblue]Case[/color] "Yes"
             [color=darkblue]With[/color] r.EntireRow
                .Locked = [color=darkblue]True[/color]
                .FormulaHidden = [color=darkblue]True[/color]
             [color=darkblue]End[/color] [color=darkblue]With[/color]
        [color=darkblue]Case[/color] Else:
             [color=darkblue]With[/color] r.EntireRow
                .Locked = [color=darkblue]False[/color]
                .FormulaHidden = [color=darkblue]False[/color]
             [color=darkblue]End[/color] [color=darkblue]With[/color]
    [color=darkblue]End[/color] [color=darkblue]Select[/color]

    [color=darkblue]With[/color] r.EntireColumn
        .Locked = [color=darkblue]False[/color]
        .FormulaHidden = [color=darkblue]False[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    Debug.Print r.Address
[color=darkblue]Next[/color] r

Me.Protect DrawingObjects:=True, Contents:=[color=darkblue]True[/color], Scenarios:=True

Application.ScreenUpdating = True

[color=darkblue]End[/color] [color=darkblue]Sub[/color]