VBA Locking Range, if cell date value is less than today.

Greetings, hope all is well!

Trying with VBA to lock range from to B:K, provided that the date of K is less than today.

Can you please help me with a code that will be applied to the active sheet 'ranges'? and whenever new rows are filled?


Thank you very much in advance!

Hi there. This isn't difficult, but there are a few little pitfalls to avoid.

Firstly, to "lock" cells the worksheet needs to be protected.

• All cells are locked by default, so this means
  • You need to decide which cells should be editable by the user
  • You need to control which cells are editable by the user
• There needs to be a mechanism to add data to your sheet
  • To add lines to the data the worksheet needs to be unlocked first
  • data must be added in unlocked cells while the other cells remain locked to the user
    

This could be accomplished by unlocking the whole columns B:K - that's rather untidy, but can be OK. What I've done in the sample, however, is convert the range of data to a table. Tables are easy to reference, and can be expanded easily.

The actual locking is in the "Workbook_Open()" function:

Please Login or Register  to view this content.

This simply finds the last row in column K, iterates through the cells above it and locks those cells if the date in K is earlier than today's date.

To add a new row to the table for more data this routine will suffice - it unprotects the worksheet, adds a new row to the table, and re-protects the worksheet afterwards:

Please Login or Register  to view this content.

So it's easy to see which cells are locked or unlocked I've added conditional formatting to all cells. I know this may be over-kill for what you need!

HTH
Tim