How to lock cells after a date has passed

[QuintonMcCloud]

Hi guys I a basic excel user and I have hit a wall. I have created a stock taking work book for a university project that I am doing. I chose to do a simple stock take file for a Timber Frame company. I want to lock a specific 'named range' column after the month has passed so for example:

STOCK TAKE/COUNT- this page will be for inputting data so if you are counting stock this is the page that you will see however once you have input that data there is a SAVE macro that I created and assigned to an image on this COUNT page and this copies a TOTALS column in the COUNT page and pastes it onto a column in the MONTHLY Page.

TimberMONTHLY- this is the worksheet where all the totals will go and on this page it lists all the materials in exactly the same way that they are listed on the STOCK TAKE page with the only difference being that the monthly page has JAN - DEC monthly columns.

Notes: now the assumption for the project is that the stock take is done at the end of each month. So at the end of April for example there will be a stock take, say, on the last working day of the month or just on the last day. I want my macro to save data into the correct month column and once that data is saved to also be able to lock that column. so at the end of april once all data has been collected if i click save I want just the values of that data to be copied onto the April column and then April is locked but all other months after that are open. I want the macro to just save data into the correct columns.

I hope this is not too complex I just wish I knew a lot more vba perhaps or had a lot more advanced excel knowledge.

I have attached the file so what I am saying doesnt sound like jibberish.

Oh and its EXCEL 2007 btw

[ARowbot]

This only covers the Timber one but it is easily modified! Also currently alot of other cells have the locked property so that needs to be unchecked before running the macro or all cells will be locked Hope this is does what you want.

Please Login or Register  to view this content.

[QuintonMcCloud]

Thank you so much ARowbot. And thank you for noticing as well that the STOCK COUNT page is just each material page basically.
I have run the macro and it brings back RUNTIME ERROR '1004' and says 'unable to set the locked property of the Range class'. I have gone into the TimberMONTHLY worksheet and selected all of the month columns and unlocked them and then unprotected the worksheet and the error still comes up. I think perhaps there is something that I am doing wrong.

[ARowbot]

Indeed! I didn't realise that there were merged cells in that column so therefore it didn't work! Here is the updated code:
Improvement: I have put p As a variable so that you don't need to physically count how many items there are in the category.

Please Login or Register  to view this content.

[QuintonMcCloud]

ARowbot you are a life saver. The codes you are sending work absolutely fine however once I have clicked the save icon and the data has been saved onto the TimberMONTHLY page an error message still pops up for some reason. In this case a new error message that has come up is 'application defined or object defined error' . I think perhaps i need to check and see whether the code is in standard module or worksheet module. Is it possible to input it into the file and then attach it onto here ARowbot. And also because i unlocked the month columns and then unprotected the worksheet, when i run the macro it does post it into April however it does not lock previous months and also what happens on the 1st of May for example if I was to repeat the same process and click save will it then enter it into MAY?

Apologies for being an absolute pain in the back side btw

[QuintonMcCloud]

Sub TimberSAVE()
Dim i, p As Integer
' TimberSAVE Macro
For i = 5 To 16

If Sheets("TimberMONTHLY").Cells(4, i) = UCase(Format(Now, "mmm")) Then
p = Sheets("TIMBER").Range("G" & Rows.Count).End(3).Row
Sheets("TIMBER").Range("P5:P" & p).Copy
Sheets("TimberMONTHLY").Cells(5, i).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Sheets("TIMBER").Range("J5:N" & p).ClearContents
Sheets("TimberMONTHLY").Range(Cells(5, i), Cells(p, i)).Locked = True
Sheets("TimberMonthly").Protect
Exit Sub
End If
Next i
End Sub




I have underlined where the error is coming from I hope it shows on your end. Its immediately after the contents have been cleared. I really need to master my vba more and more

[ARowbot]

I have attached the workbook that seems to work for me. Slightly adapted to lock all columns before aswell.
The problem being is that if you do it on the 1st of May then yes April will be locked because how it is coded. This could be changed if necessary...

[ARowbot]

UPDATE: I am getting the error...But I can't for the life of me figure out why it won't let me select a cell after the copy and paste.

[QuintonMcCloud]

Thank you very much ARowbot I will run it and get back to you. Thank you for your help and understanding. Have a good evening

[ARowbot]

Not sure what I did but I don't seem to get the problem anymore...

[QuintonMcCloud]

Good morning Arowbot. Yeah I am going to try and have a look as well and see if there is anything that flags up. If not and the new one you sent doesnt bring up the error then its super thank you very much though for your help I appreciate that. Ill give you feedback once I have run it.