could someone tell me how to lock cells automatically in excel.
i have a date field in column a and i want the code to look at the cells in column a and lock the whole row if that cell is greater that 1 week old.
i dont use a password in the protect sheet but i just want to limit accidental deletions or alterations.
thanks
If you lock a cell without protecting the sheet then it won't do anything, although you can protect the sheet without a password.
You might be able to achieve this with some sort of data validation if you don't protect the sheet.
i use the protect sheet fuction within Excel, but i dont put any text in. But if code needs a pwsd then i will use one. like i said, its mainly to stop people from changing data accidently as they will get a prompt saying that the cells are protected.
i just want the cells to lock automatically if they are over a week old. i.e the sheet is protected, but only rows where the date in a is greater than 1 week... something like
(i know this code wont work but its an example, i want it to lock the whole row)PHP Code:Private Sub Worksheet_Calculate()
If Range("A:A").Value <today() Then
Me.protect Password:=""
Range("A:A").Locked = False
Me.Protect Password:=""
End Sub
Last edited by wayneg; 12-17-2013 at 06:21 AM.
Well the code would be something like:
You would need to assign it to an event, it could be when you open the workbook, when you change a cell etc. I would suggest that running it everytime the workbook opens is probably ok? For that you need:
Please Login or Register to view this content.
saved in the "thisworkbook" object module.Please Login or Register to view this content.
i cant seem to get that to work. could you upload a sample please
Hi,
Sorry uploads are not working for me at the moment, I had a look and made some changes to the code:
Change the sheetname as neccessary.Please Login or Register to view this content.
where am i adding this as i tried on sheet1 , tried on thisworkbook and tried as a module and then saved it, but i can still edit the rows where there is a date older that today. sorry to be a pain in the ..... lol
thisworkbook.
It will run when the workbook opens - you can press f5/click run in the macro manually to test though. Maybe if you try uploading your workbook with the macro as you are trying to use it I can have a look.
i think i have managed to do it now. thank you
ive realised that after i marked this as solved it doesnt work on the values that is part of a table. it works on the values that isnt in a table so is there anything different i need to do.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks