Protecting workbook Based on Dates.

[chrisumali]

Good day to all,

Please help me with this problem, I am trying to create excel accounting system from scratch. And I attached the sample file. The file is protected password is "a" without quotation mark.

I want a macro that will protect column for "DEBIT,CREDIT and PARTICULAR" which are < the current date. My objective is to prevent user of the program to alter what they have done previously. the only thing open for data entry is the row which correspond to the current date. (Yesterday Locked, Tomorrow Locked).

And of course even the three columns are open the "BALANCE" column is Locked.

I hope it is possible since I have 6 sheets that need to be coded.

If possible please run this macro every time the file is opened.

Also to all user if you have accounting system can I have a copy so that I can learn, edit or pattern what I am doing.

Thanks

[rwgrietveld]

For the future, please upload just a part of the Sheet that explains the problem.

Next: Keep in mind that working with excel should be fun and intuitive.

For the code you require:

Sub ProtectSht(Ws As Worksheet)

Dim DateRng As Range, Ccell As Range

Ws.Unprotect "a"
Set DateRng = Range(Range("A1"), Range("A65536").End(xlUp))

DateRng.EntireRow.Locked = True 'All Locked
For Each Ccell In DateRng
  If Format(Ccell.Value, "dd/mm/yy") = Format(Now(), "dd/mm/yy") Then
    Ccell.EntireRow.Locked = False
  End If
Next

ActiveSheet.EnableSelection = xlUnlockedCells
Ws.Protect "a"

End Sub

This code can be called on activation.

Private Sub Worksheet_Activate()
  Call Module1.ProtectSht
End Sub

[teylyn]

Keep in mind that working with excel should be fun and intuitive.

... really?!