Lock tab after date shown in cell B1 which is different for each tab

When the workbook is opened I need all the tabs which are past the month end date shown in cell B1 (different on each tab) to lock all the unprotected cells on said sheet. I need to be able to prevent changes to previous months reporting.

The current password Mega123.

I do not have alot of experience with VBA codes & such, so any help would be very appreciated!

Thank you!

Hi and welcome

Paste the below into the ThisWorkbook module, then save the file, close and reopen.

Please Login or Register  to view this content.

See attached for the code in place.

BSB

Hmmm... this is not working for me. I am still able to change / input information even though the dates are passed.

on the first tab, I double clicked on ThisWorkbook module & pasted the code above code, saved & reopened.

Any suggestions?

I'd need to see your file with the code in place to work out why it's not working for you.

Did you try the one I attached?

BSB

I attached the file with the code you suggested.

When I open that, all the sheets that I'd expect to be protected are protected and I can make no changes, so looks like the code works fine to me.

BSB

I also tried the one you attached, but If there are more months added, would need to be able to enter data into them.

The code checks if the date in B1 on each sheet is less than the current date, then it protects it. If it's not less than the current date then it leaves it unprotected. So adding more months would allow users to update them, until the date in B1 is in the past.

BSB

Sorry If I am confusing...

I attached another file with your code, but 2 add'l tabs which I need to remain editable until the month end date shown in B1.

Thank you for your help!

And in that version I can edit the June and July sheets. As of Tuesday I will no longer be able to edit the June one, but still edit the July one.

All seems to be working fine for me.

If it's not for you then ensure you click "Enable Macros" if asked when opening.

BSB

I can still edit the cells that I need protected in Jan, Feb, Mar tabs... Column AL cells 4,5,6,7,8 - Clolums E thru AF row 12,14,16,18, etc... basically all the uncolored cells on the entire sheet I need protected unless the date HAS NOT passed.

Ahh OK, I hadn't tested every cell

I can see it's not doing what it's supposed to when I try to change those cells. Not 100% sure why though.

Found this down the back of the internet... "Bug: There have been reports of bugs where even with locked cells, users can still edit them. This may require creating a support ticket with Microsoft to investigate further"

Could be that if you built the thing from scratch and use that going forward it may resolve itself, but there's no guarantee.

BSB