VBA Lock Cells - Error Type 13 Mismatch - Multiple Cells

jacoblewis1994

New Member
Joined
Dec 15, 2015
Messages
4
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.

Code:
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:

Code:
Case "Yes"
Any help would be appreciated.

Thanks
 

Sixthsense

Active Member
Joined
Nov 19, 2012
Messages
385
Try this... :rolleyes:

Code:
[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]
 
Last edited:

jacoblewis1994

New Member
Joined
Dec 15, 2015
Messages
4
I now get this error:

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

jacoblewis1994

New Member
Joined
Dec 15, 2015
Messages
4
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

New Member
Joined
Dec 15, 2015
Messages
4
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

Active Member
Joined
Nov 19, 2012
Messages
385
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 :p

Code:
[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]
 

Forum statistics

Threads
1,086,125
Messages
5,387,986
Members
402,094
Latest member
Bloody69

Some videos you may like

This Week's Hot Topics

Top