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]

I may have a solution for this but I must wait until tomorrow to be sure.
Anyway I'll offer the following code with one important note: B1 must have something in it like "Record"/"Inventory" or whatever.
You must also Highlight the whole sheet, right click and select Format cells, select Protect and uncheck the locked option.
Add the code to VBA and then select Review/Protect Sheet. Then save and close the file.

If my advice is correct auto_lock is activated, will lock the prior date row and set the sheet up for whatever "TODAY" happens to be.

Sub auto_lock()
'
' LockMe Macro
'

'
 lastrow = Cells(Rows.Count, "B").End(xlUp).Row
    Rows("4:4").Select
    Selection.Locked = True
    Selection.FormulaHidden = False
    Range("B" & lastrow).Formula = "=Today()"
    Range("F" & lastrow).Formula = "=Today()"
    Range("H" & lastrow).Value = Range("G" & lastrow).Value - Range("C" & lastrow).Value
    
   
End Sub

 

[RAMU]

I may have a solution for this but I must wait until tomorrow to be sure.
Anyway I'll offer the following code with one important note: B1 must have something in it like "Record"/"Inventory" or whatever.
You must also Highlight the whole sheet, right click and select Format cells, select Protect and uncheck the locked option.
Add the code to VBA and then select Review/Protect Sheet. Then save and close the file.

If my advice is correct auto_lock is activated, will lock the prior date row and set the sheet up for whatever "TODAY" happens to be.

Sub auto_lock()
'
' LockMe Macro
'

'
 lastrow = Cells(Rows.Count, "B").End(xlUp).Row
    Rows("4:4").Select
    Selection.Locked = True
    Selection.FormulaHidden = False
    Range("B" & lastrow).Formula = "=Today()"
    Range("F" & lastrow).Formula = "=Today()"
    Range("H" & lastrow).Value = Range("G" & lastrow).Value - Range("C" & lastrow).Value
    
   
End Sub

Hi,

I tried as per your instruction but not a single cell is locked. May be my process was wrong.

 

[BrianJN1]

Hours after I posted I realised that the =TODAY formula is not updated. That value should be converted to a specific value.
Then I should have run a test to ask if the date in the last column is TODAY. If it is not then the macro would lock the last line before adding the new line.

I shall relook at that but I'm still dubious if Excel does recognise that auto_lock() is meant to run automatically upon opening.
My apologies for my oversights.

 

[BrianJN1]

I am about to leave home and will not return until later in the day but I have something that I believe now works.
I'll explain then.

 

[RAMU]

Am awaiting friend

 

[RAMU]

its ok. i will do that.

 

[RAMU]

its ok, i will do that.

Hours after I posted I realised that the =TODAY formula is not updated. That value should be converted to a specific value.
Then I should have run a test to ask if the date in the last column is TODAY. If it is not then the macro would lock the last line before adding the new line.

I shall relook at that but I'm still dubious if Excel does recognise that auto_lock() is meant to run automatically upon opening.
My apologies for my oversights.

 

[BrianJN1]

I'm not sure if this is shared. I hope so. In any case the file is set up exactly as I mentioned above except that I put =TODAY in A1
The macro needs to see that. The macro also uses the Review>Protect/Unprotect features automatically.


This is the altered code:

Sub auto_lock()
'
' LockMe Macro
'

'
 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
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    End If
   
End Sub

Sub overwrite()
'
' overwrite Macro
'

'
    Range("B4").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("F4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

Tell me if you have issues.

Dropbox - LockCells.xlsm

 

[BrianJN1]

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.