Lock cells

[snoozee]

Hi

I've had a search through the forums for a couple of hours yet can't seem to find anything that matches what I'm looking to do and my VBA skills are a little rusty after not using them for a number of years

I'm creating a worksheet as follows

A B C D E F
Monday Tuesday Wednesday Thursday Friday
Task 1
Task 2
Task 3
Task 4

Date

In an ideal world the user would enter "Y" into the relevant cell to indicate the task as being completed for that day.

What I need is

1. When the user enters "Y" into any task cell that a date stamp (today's date) appears in row 7 for that particular day

After they have finished updating the tasks for that day I want them to click a "button" (CommandButton?) and when this button is clicked I need the following to happen

1. All cells in the rows relating to that particular day (incl the date) are locked regardless as to whether they all contain "Y"
2. A message appears asking them if they are sure they want to exit
3. YES - takes them to the "Home" worksheet
4. NO - takes them back to the sheet they are currently using

Is all of this possible ??

 

[BenGee]

Hi snooze, If I've understood right then try this.

Paste this code in the sheet code;

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Dim r As Range
Set r = Range("B2:F5")
Application.EnableEvents = False
For Each c In r
If c = "Y" Then
    Cells(7, Target.Column) = Format(Now(), "Short Date")
End If
Next
Application.EnableEvents = True
End Sub

And paste this in your command button module;

Sub Button1_Click()
Response = MsgBox("Are you sure you want to exit?", vbYesNo)
If Response = vbNo Then Exit Sub
If Response = vbYes Then
    ActiveSheet.Protect Password:="Password", DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
    
    Worksheets("Home").Activate
End If
End Sub

Is this what you were looking for?