Lock Cells Based on Date Value in Another Cell

nau2002

Board Regular
Joined
Dec 19, 2016
Messages
97
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:

ABCDENO
1PeriodMar-17
2Measurement:TargetJan-17Feb-17Mar-17Dec-17YTD
3Attrition5-10%3.9%5.5%4.1%4.5%
4Target Min5%5%5%5%
5Target Max10%10%10%10%
6Utilization>80%104%96%98%101%
7Target80%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:


ABCDEFG
1Period:2017-Q2
2MeasurementTarget2017-Q12017-Q22017-Q32017-Q4YTD
3Attrition5-10%3.9%
4Target Min5%5%5%5%5%
5Target Max10%10%10%10%10%
6Utilization>80%98%
7Target80%80%80%80%80%

<tbody>
</tbody>


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

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Watch MrExcel Video

Forum statistics

Threads
1,122,471
Messages
5,596,342
Members
414,060
Latest member
hermanseck

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top