Lock cells in a column after day end

[jgopal]

Hello Friends,

I am trying to make a report tracking spreadsheet. The workbook currently contains a Daily Dashboard (which is working fine thanks to help from Dominic) and the 12 months of the year. Every month worksheet is the same and has a list of reports, with target time that the report has to be sent with all days of the month. User updates the time he/she sends the report daily. The daily dashboard is dynamic capturing the daily input of the user for the Manager to see the status of the current day.

Now, to ensure control on the data entry, we want to lock all the time input cells of the day at end of day which is 00:00 hrs of the following day. Basically, input column under 11/04/2022 should get locked when it is opened on 12/04/2022. I found a few macros when I searched but nothing specifically for my application, hence I believe it can be done. I probably will have to apply it to all 12 worksheets manually by changing the sheet name.

If the xl2bb capture is working, you all will be able to see that all cells except the time input cells are locked for the data entry.
Hope my requirement is clear and you guys can help us with this. If not, please let me know and I will try to explain further.

It looks like everyday there are new requirements coming up, which I will post as separate threads if I am unable to find a solution online. Looks like this project is going to end up being a software. So appreciate all your help and knowledge in such advanced excel solutions.

Thanks
Best Regards
J

Report Tracker.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
3	DAY		Friday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
4	CODE	TIME	01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16
5	D1-1	11:30			WEEKEND					13:00		WEEKEND
6	D1-2	11:30								12:00
7	D1-21	11:30								11:00
8	D1-22	11:30								11:35
9	D1-3	11:30								12:01
10	D1-4	15:00
11	D1-5	15:00
12	D1-6	11:30
13	D2-1	9:30
14	D2-2	9:30
15	D2-3	12:00
16	D3-1	15:00
17	W1-1
18	W1-2	11:30
19	W1-3	11:30
20	W1-4	11:30
21	W1-5	11:30
22	W1-6	11:30
23	W2-1
24	W2-2	11:30
25	W2-3	11:30
26	W2-4	11:30
27	W2-5	11:30
28	W3-1
29	W3-2	14:00
30	W3-3	14:00
31	U1-1
32	U1-2
33	U1-3	17:00
34	U1-4	17:00
35	U1-5	7:12
36	U1-6
37	U1-7
38	U1-8
39	U1-9
April

Cell Formulas
Range	Formula
C3:R3	C3	=TEXT(C4,"dddd")

 

[mumps]

Start by unlocking all the cells in all 12 sheets. Place this macro in the code module for ThisWorkbook. Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the left hand pane, double click on "ThisWorkbook". Copy/paste the macro into the empty window that opens up. Close the window to return to your sheet. Save the workbook as a macro-enabled file. This macro will run automatically when you open the file and will lock the cells in all worksheets. In order to lock the cells, the macro will need protect all 12 sheets. As currently written, the macro does not use a password to do the protection. If you want to use a password, please let me know. If there are any sheets that need to be excluded, please post those sheet names.

Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    Dim foundDate As Range, ws As Worksheet
    For Each ws In Sheets
        ws.Unprotect
        Set foundDate = ws.Rows(4).Find(Date, LookIn:=xlFormulas, lookat:=xlWhole)
        If foundDate.Offset(-1, -1) = "Sunday" Then
            ws.Columns(foundDate.Column - 2).Locked = True
        Else
            ws.Columns(foundDate.Column - 1).Locked = True
        End If
        With ws
            .Protect
            .EnableSelection = xlUnlockedCells
        End With
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[jgopal]

Hi Mumps,

I haven't tested your macro code yet as I do have one other worksheets and probably will have more worksheets that will extract data from these 12 months (please see the attached screen grab which shows the other two sheets. Currently, there is Daily Dashboard sheet which populates the current day's status of the daily reports being sent.

There will be a Weekly Dashboard or maybe Weekly Report sheet that I have to start work on too which will have the same columns like code, tartget time and 6 columns for time values extracted from the month sheets based on a date range which would typically but not limited to the 6 days of the past week. I say not limited because at any given time, if the team lead wishes to view the report for a another week, it should be able to do that too. Then there might be a quarterly report as I expect more new requirements as this excel workbook turns into a 'software'.

As for passwords for worksheet protection, yes, all worksheets will be protected with the same password to avoid any accidental changes or deletions in formulas, formatting, etc.

Thanks
Regards
J

 

[jgopal]

Hi again,
I tried the above and am getting the following error when I open the workbook
I had another macro already running to activate the Daily Dashboard worksheet when the workbook opens like below:

Private Sub Workbook_Open()

Worksheets("Daily Dashboard").Activate

End Sub

I thought this could be the cause of the error, so I removed that completely to see what is the result of your code.

Please let me know if I am doing anything wrong.

Regards
J

 

[mumps]

I tested the macro using the data you posted in your original post and it worked properly. Try this revised version:

Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    Dim foundDate As Range, ws As Worksheet
    Sheets("Daily Dashboard").Activate
    For Each ws In Sheets
        If ws.Name <> "Daily Dashboard" Then
            ws.Unprotect
            Set foundDate = ws.Rows(4).Find(Date, LookIn:=xlFormulas, lookat:=xlWhole)
            If foundDate.Offset(-1, -1) = "Sunday" Then
                ws.Columns(foundDate.Column - 2).Locked = True
            Else
                ws.Columns(foundDate.Column - 1).Locked = True
            End If
            With ws
                .Protect
                .EnableSelection = xlUnlockedCells
            End With
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

If it still doesn't work, perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here (de-sensitized if necessary).

 

[jgopal]

I tested the macro using the data you posted in your original post and it worked properly. Try this revised version:

Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    Dim foundDate As Range, ws As Worksheet
    Sheets("Daily Dashboard").Activate
    For Each ws In Sheets
        If ws.Name <> "Daily Dashboard" Then
            ws.Unprotect
            Set foundDate = ws.Rows(4).Find(Date, LookIn:=xlFormulas, lookat:=xlWhole)
            If foundDate.Offset(-1, -1) = "Sunday" Then
                ws.Columns(foundDate.Column - 2).Locked = True
            Else
                ws.Columns(foundDate.Column - 1).Locked = True
            End If
            With ws
                .Protect
                .EnableSelection = xlUnlockedCells
            End With
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

If it still doesn't work, perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here (de-sensitized if necessary).

Thank you. let me try it it and I will let you know.
If it doesn't work or throws some error, I will upload and share the link. Nothing to de-sensitize!!!

 

[jgopal]

Hi

Tried it but gave me the error. I am sharing the file on Dropbox for you to check.
Also attaching the pic of the error.


1. We don't want the users to changes the formatting, text changes in the titles or target time cells. Hence all worksheets require to be password protected allowing the users to enter only the time in the current day's cell of every month against the report that is complete and sent. So is it necessary that all cells of the 12 month sheets are unlocked or is there a way around that?

2. Daily Dashboard is locked and its only for viewing by the Manager to see the status.

3. Weekly report will be locked except for input of start date. Team leader will use this to send the report status of the week.

Thanks
Regards
J

 

[mumps]

Start by protecting all your monthly sheets. Then use this macro in the code module for ThisWorkbook. Change the password (in red) to suit your needs. Save the file, close it and re-open it. The macro will lock all cells in the sheet for the current month except for the cells for the current date, starting in row 5.

Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    Dim foundDate As Range, ws As Worksheet, mon As String, lRow As Long
    mon = MonthName(Month(Date))
    Sheets("Daily Dashboard").Activate
    With Sheets(mon)
        .Unprotect Password:="MyPassword"
        lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Set foundDate = .Rows(4).Find(Date, LookIn:=xlFormulas, lookat:=xlWhole)
        .Cells.Locked = True
        .Cells(5, foundDate.Column).Resize(lRow - 4).Locked = False
        .Protect Password:="MyPassword"
        .EnableSelection = xlUnlockedCells
    End With
    Application.ScreenUpdating = True
End Sub