VBA Locked Rows conditional on cell's left characters

[Kencrossley]

After reviewing and experimenting with a few different suggested codes, I decided to post a thread to ask for help in cleaning up and solving my problem.

I'm trying to lock the rows to the right of a target cell. The list is a reoccurring list of exact days and dates, followed by a weekly summary. The target cell is indicated by a conditional "week"+an increasing#. The "week" row is inserted after every sunday. The week row is dynamic and changes location based on year or month.

I've been using a Left(formula to identify "week", but it doesn't seem to be executing.

Right now the sheet reads:

​

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F...[/TD]
[TD="align: center"]O[/TD]
[/TR]
[TR]
[TD="align: center"]Dates[/TD]
[TD="align: center"]Sales[/TD]
[TD="align: center"]$[/TD]
[TD="align: center"]%[/TD]
[TD="align: center"]Store[/TD]
[TD="align: center"]Total[/TD]
[/TR]
[TR]
[TD="align: center"]Saturday 11/23/13[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[/TR]
[TR]
[TD="align: center"]Sunday 11/24/13[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]y[/TD]
[TD="align: center"]y[/TD]
[TD="align: center"]y[/TD]
[TD="align: center"]y[/TD]
[/TR]
[TR]
[TD="align: center"]Week #1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]z[/TD]
[TD="align: center"]z[/TD]
[TD="align: center"]z[/TD]
[TD="align: center"]z[/TD]
[/TR]
[TR]
[TD="align: center"]Monday 11/26/13[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]a[/TD]
[/TR]
</tbody>[/TABLE]











The base code was copied from an code LENZE posted a few years ago.

Private Sub WorkSheet_Change(ByVal Target As Range)
If Target.Column <> 2 Then Exit Sub
ActiveSheet.Unprotect
Select Case Target
Case Left(b12, 4) = "Week"
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


Any help would be greatly appreciated.

 

[Teeroy]

Welcome to the MrExcel Forum.

The Select Case statement is slightly off. You evaluate the condition first, then check its result. Try:

Private Sub WorkSheet_Change(ByVal Target As Range)

 If Target.Column <> 2 Then Exit Sub
 ActiveSheet.Unprotect
 Select Case Left(Target, 4)
 Case Is = "Week"
    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

 

[Kencrossley]

Thank you for your help cleaning up the code. Unfortunately, I can't get the Private sub to run, or if it is running, I can't get the appropriate cells to lock out. Any suggestions?

 

[Teeroy]

This code must be stored in the sheet code module, not a standard module, to run as an event. Have you put it in this location?