Lock the particular cells after date is over

[RAMU]

Hi Friends,

Hv a workbook with multiple sheets (months).

In july sheet I need these feature:

July

	A	B	C	D	E	F	G	H	I	J	K
	START				FINISH
	DATE	TIME			DATE	TIME		HOUR		QTY

<colgroup><col style="WIDTH: 30px; FONT-WEIGHT: bold"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]25-07-17[/TD]
[TD="align: right"]10:30[/TD]

[TD="align: right"]25-07-17[/TD]
[TD="align: right"]18:15[/TD]

[TD="align: right"]7:45[/TD]

[TD="bgcolor: #99ccff, align: right"]30[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #ffff00"] [/TD]

[TD="bgcolor: #ffff00"] [/TD]

[TD="bgcolor: #99ccff"] [/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #99ccff"] [/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #99ccff"] [/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="bgcolor: #99ccff"] [/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="bgcolor: #99ccff"] [/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="bgcolor: #99ccff"] [/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="bgcolor: #99ccff"] [/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B4 =TODAY() F4 =TODAY() I4 =+G4-C4 <tbody> </tbody>

<tbody>
</tbody>

In Row 4 Today's data will be entered. Tomorrow or any day after today when the workbook will be opened Row 4 will become locked automatically. only admin will be able to unlock that.

At the same time B5 & F5 (yellow highlighted) will show the date, on the day the workbook will be opened but if I open today again or the same day it will not show. Then someone will enter the data on that day. when the day will over, again the Row 5 will be locked automatically, that means after 26th or any day the row 4 & 5 will be locked, rest will remain as unlocked.

pls help me to provide a solution.

Regards
RAMU

 

[BrianJN1]

Ah, solved.
Open the Workbook.
Press ALT+ F11.
Doubleclick “ThisWorkBook” in the left side panel
Copy the code and paste it into the large white editor space and then save the workbook.

Private Sub Workbook_Open()

ActiveSheet.Unprotect
 lastrow = Cells(Rows.Count, "B").End(xlUp).Row
    
    'Selection.FormulaHidden = False
    If Range("B" & lastrow).Value <> Range("A1").Value Then
    Rows(lastrow & ":" & lastrow).Select
    Selection.Locked = True
    Range("B" & lastrow + 1).Formula = "=Today()"
    Range("F" & lastrow + 1).Formula = "=Today()"
    Range("B" & lastrow + 1).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("F" & lastrow + 1).Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
        Range("H" & lastrow + 1).Select
    ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-5]"
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    End If



End Sub

If you are using the copy from Dropbox then simply saving should be enough otherwise it must be saved as macro enabled, ie, with an “xlsm” extension.

NOTE somewhere I deleted the Formula for Col H by mistake, it is now back.

 

[BrianJN1]

Fine tuning:
Delete
"ActiveSheet.Unprotect" and

"Selection.Locked = True" from the macro otherwise cells that are supposed to have been locked are actually unlocked!"

 

[RAMU]

Can you pls allow me to share my original file ? I am little confused, that's why requesting.

The macro works as required except .... it does NOT autorun when the file is opened; that has been my concern from the start.
I presume that you are aware that you can add a password using the Review>Protect option.

It may be necessary to attach the macro to a button but that would still leave "yesterday's" data vulnerable until the macro is run.

 

[BrianJN1]

By all means, I have been working as best I could from what you were describing.
Now, if that file is reasonably sensitive I will accept a PM.