Lock down cells

[EmmaTM]

Hi,
I have this dropdown on a tab of my workbook, I want to lockdown all cells on the second tab depending on what date I have chosen from the first dropdown.
So for example the dropdown is set now to 14/4/24 so I want all the cells on my spreadsheet locked other than H14:H85. Is that possible?

Thank you

 

[myall_blues]

Try this - copy and paste this macro into the worksheet code module of the sheet that contains the date dropdown. To do that: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Now go back to the sheet and change the date in the dropdown.

You will need to change the name of the sheet containing your hours from "Sheet2" to whatever you have called it at the location I have shown.

Private Sub Worksheet_Change(ByVal Target As Range)
    Const DataSheet As String = "Sheet2"    '<== Change to the name of the sheet containing your hours
    Dim i As Long
    Dim chCell As Range, chRng As Range
    Dim RngString As String
    If Intersect(Target, Range("B16")) Is Nothing Then Exit Sub
    Worksheets(DataSheet).Unprotect
    Worksheets(DataSheet).Cells.Locked = True
    For i = 8 To 18
        If Worksheets(DataSheet).Cells(9, i).Value = Range("B16").Value Then
            RngString = Cells(14, i).Address & ":" & Cells(85, i).Address
            Worksheets(DataSheet).Select
            Worksheets(DataSheet).Range(RngString).Locked = False
        End If
    Next i
    Sheets(DataSheet).Protect
End Sub

 

[EmmaTM]

Thank you for your reply, the date dropdown is on a tab called 'Front Sheet' and is now in cell L2, so I have renamed sheet 2 and amended B16.
The data I want locked is on a different tab called 'TS' - do I need to change anything else to reflect this?

 

[myall_blues]

You don’t need to do anything for ‘Front Sheet’ other than make sure the code is in that sheet (so that it catches when the dropdown changes) and amend B16 as you already have.

This line:
Const DataSheet as String = “Sheet2”

should now read

Const DataSheet as String = “TS”

 

[EmmaTM]

Sorry if I have misunderstood but the 'Front Sheet' tab where I have the dropdown has been locked and not the 'TS' tab. Is there something else I need to change?

Private Sub Worksheet_Change(ByVal Target As Range)
Const DataSheet As String = "TS"
Dim i As Long
Dim chCell As Range, chRng As Range
Dim RngString As String
If Intersect(Target, Range("L2")) Is Nothing Then Exit Sub
Worksheets(DataSheet).Unprotect
Worksheets(DataSheet).Cells.Locked = True
For i = 8 To 18
If Worksheets(DataSheet).Cells(9, i).Value = Range("L2").Value Then
RngString = Cells(14, i).Address & ":" & Cells(85, i).Address
Worksheets(DataSheet).Select
Worksheets(DataSheet).Range(RngString).Locked = False
End If
Next i
Sheets(DataSheet).Protect
End Sub

 

[myall_blues]

No it should work. Here is the knowledge/assumptions I am working from. Apologies if this sounds "patronising" - I just want to make sure we're on common ground.
1. Sheet "Front Sheet" contains the dropdown in cell L2
2. Sheet "TS" contains your hourly data table.
3. The dates to match with the dropdown value are in sheet TS, H8:R8

In the VBA editor, it will look something like this:

Right click on 'Front Sheet', then select 'View Code'
Paste the code you have into the blank window that appears.

Now starting from my very cut down version of your pages, and with both sheets unlocked.
Select a date from the dropdown:

Book1
	K	L	M
1
2		21/04/2024
3
4
Front Sheet

Cells with Data Validation
Cell	Allow	Criteria
L2	List	=$N$3:$N$5

Cells as shown on TS are locked and unlocked. 'Front Sheet' remains unlocked. Now that 'Front Sheet has become locked somehow you will need to unlock it manually (Unprotect sheet).

Book1
	H	I	J
8
9	14/04/2024	21/04/2024	28/04/2024
10
11
12
13
14	Locked	Unlocked	Locked
15	Locked	Unlocked	Locked
16	Locked	Unlocked	Locked
17	Locked	Unlocked	Locked
18	Locked	Unlocked	Locked
TS

By the way, you can remove this line of the code if you want. I had it in testing and forgot to remove it.

Dim chCell As Range, chRng As Range

 

[EmmaTM]

Thank you Murray,
Not patronising at all and thank you for explaining.
This is all working as I had requested.

How can I leave cell S2 on the TS tab as unprotected? If at any point during the period the contractor has completed the works he would select the tick on the dropdown in cell S2.

Many thanks for your help

 

[myall_blues]

That’s great news, pleased I could help.

Add line as indicated to keep S2 unlocked.

Next i
Worksheets(DataSheet).Range(“S2”).Locked = False ‘ <== Add this line
Sheets(DataSheet).Protect
End Sub

 

[EmmaTM]

Would you mind checking as I have a run-time error '1004'


Private Sub Worksheet_Change(ByVal Target As Range)
Const DataSheet As String = "TS"
Dim i As Long
Dim RngString As String
If Intersect(Target, Range("L2")) Is Nothing Then Exit Sub
Worksheets(DataSheet).Unprotect
Worksheets(DataSheet).Cells.Locked = True
For i = 8 To 18
If Worksheets(DataSheet).Cells(9, i).Value = Range("L2").Value Then
RngString = Cells(14, i).Address & ":" & Cells(85, i).Address
Worksheets(DataSheet).Select
Worksheets(DataSheet).Range(RngString).Locked = False
End If
Next i
Worksheets(DataSheet).Range(“S2”).Locked = False
Sheets(DataSheet).Protect
End Sub

Thank you

 

[Joe4]

Would you mind checking as I have a run-time error '1004'

On which line?
If you hit the "Debug" button, it should go to/highlight the offending line of code.

Note if it is this line here:
Worksheets(DataSheet).Range(“S2”).Locked = False
it may be because you are using slanted double-quotes (”) instead of straight ones (").
Excel does not like slanted double-quotes.