Lock Cells Based on Date Value in Another Cell

[nau2002]

Hello,

I have some code for a worksheet change event that consists of two macros to prevent changes to a 'closed' period within my spreadsheet and then to prevent users from overwriting formulas. The period was in Cell C1. My code worked when I had my spreadsheet set up where each column represented a month. Example below:

	A	B	C	D	E	N	O
1		Period	Mar-17
2	Measurement:	Target	Jan-17	Feb-17	Mar-17	Dec-17	YTD
3	Attrition	5-10%	3.9%	5.5%	4.1%		4.5%
4		Target Min	5%	5%	5%		5%
5		Target Max	10%	10%	10%		10%
6	Utilization	>80%	104%	96%	98%		101%
7		Target	80%	80%	80%	80%	80%
8

<tbody>
</tbody>

Here is my original code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim msg As String
'Macro #1
'check if the row is greater than 1.
If Target.Row > 1 Then
'if the selected column date is less than the B1 date
If Range("C1") > Cells(2, Target.Column) Then
Application.EnableEvents = False
'undo the change
Application.Undo

Application.EnableEvents = True
'throw a messagebox
msg = MsgBox("This column is for a scorecard reporting period that is not open." & vbNewLine & " " & vbNewLine & "Please only enter data for the current reporting period, or contact the Investment Management Team for assistance." & vbNewLine & _
vbNewLine & "" & vbNewLine & "OK to move right to allowed date." & vbNewLine & "Cancel to view current reporting date.", vbOKCancel)
'stop checking for events
Application.EnableEvents = False
Select Case msg
Case 1 'OK button pressed
'loop until the B1 date is less or equal to the header date
Do Until Range("C1") <= Cells(2, ActiveCell.Column)
'shift one cell to the right
ActiveCell.Offset(0, 1).Select
Loop
Case 2 'Cancel Button pressed
'select C1
Range("C1").Select
Case Else
End
End Select
'enable event checking
Application.EnableEvents = True
End If

End If

'Macro #2
'if the selected cell is not equal to range 'Blocked'
If Not Application.Intersect(Target, Range("Blocked")) Is Nothing Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
'throw a messagebox
msg = MsgBox("You can not make changes to the formulas on this spreadsheet." & vbNewLine & vbNewLine & _
vbNewLine & "" & "Please contact the team for assistance.", vbOKCancel)
'stop checking for events
Application.EnableEvents = False
Select Case msg
Case 1 'OK button pressed
'loop until the B1 date is less or equal to the header date


Range("C1").Select

Case 2 'Cancel Button pressed
'take user to the C1
Range("C1").Select
Case Else
End
End Select
'enable event checking
Application.EnableEvents = True
End If
End Sub

I was asked to change this workbook so that it was by quarter (versus monthly). Now the code doesn't appear to fire at all and I'm presuming it's because the quarters aren't being recognized as dates but I don't know if that's really relevant to my code as someone else helped me create the code . Here's my new code that is no longer firing upon changes to a cell in a 'closed period' (e.g. C3):


Private Sub Worksheet_Change(ByVal Target As Range)
Dim msg As String
'Macro #1
'check if the row is greater than 1.
If Target.Row > 1 Then
'if the selected column date is less than the B1 date
If Range("C102") > Cells(103, Target.Column) Then
If Application.Intersect(Target, Range("Formulas")) Is Nothing Then
Application.EnableEvents = False
'undo the change
Application.Undo

Application.EnableEvents = True
'throw a messagebox
msg = MsgBox("This column is for a scorecard reporting period that is not open." & vbNewLine & " " & vbNewLine & "Please only enter data for the current reporting period, or contact the Investment Management Team for assistance." & vbNewLine & _
vbNewLine & "" & vbNewLine & "OK to move right to allowed date." & vbNewLine & "Cancel to view current reporting date.", vbOKCancel)
'stop checking for events
Application.EnableEvents = False
Select Case msg
Case 1 'OK button pressed
'loop until the B1 date is less or equal to the header date
Do Until Range("C1") <= Cells(2, ActiveCell.Column)
'shift one cell to the right
ActiveCell.Offset(0, 1).Select
Loop
Case 2 'Cancel Button pressed
'select C1
Range("C1").Select
Case Else
End
End Select
'enable event checking
Application.EnableEvents = True
End If

End If
End If

'Macro #2
'if the selected cell is not equal to range 'Blocked'
If Not Application.Intersect(Target, Range("Blocked")) Is Nothing Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
'throw a messagebox
msg = MsgBox("You can not make changes to the formulas on this spreadsheet." & vbNewLine & vbNewLine & _
vbNewLine & "" & "Please contact the Team for assistance.", vbOKCancel)
'stop checking for events
Application.EnableEvents = False
Select Case msg
Case 1 'OK button pressed
'loop until the B1 date is less or equal to the header date


Range("C1").Select

Case 2 'Cancel Button pressed
'take user to the C1
Range("C1").Select
Case Else
End
End Select
'enable event checking
Application.EnableEvents = True
End If
End Sub


And here's how the worksheet looks now:

	A	B	C	D	E	F	G
1		Period:	2017-Q2
2	Measurement	Target	2017-Q1	2017-Q2	2017-Q3	2017-Q4	YTD
3	Attrition	5-10%	3.9%
4		Target Min	5%	5%	5%	5%	5%
5		Target Max	10%	10%	10%	10%	10%
6	Utilization	>80%	98%
7		Target	80%	80%	80%	80%	80%

<tbody>
</tbody>

Anyone know why it would appear my code no longer works?