Macro to Lock cells in protected sheet depending on criteria

Hi Guys,

I have a Workbook that I use to log invoices and credit notes and it is password protected so that the cells that contain formulas are locked and therefore not selectable.

What I wanted to know is. Is it possible to lock other cells depending on what values are in certain cells?

Let me explain. I have Columns G & H which contain the heading 'Credit Note Number' & 'Credit Note Amount'. Now in columns I & J I have the heading 'Invoice Number' & 'Invoice Amount' respectively.

What I have done is to make a condional formatting so that If I put a value in cells in column G it will change the colour of the adjacent cells in columns I & J, likewise If I fill in cells in column I the conditional formatting will change the colour of adjacent cells in coulumn G & H. This helps me to see whether a row contains a credit note or an invoice.

What I would ideally like is for a Macro that would automatically locks cells G & H in a particular row that I am currently working on if I enter data into the cell in column I. Likewise if I am entering data in column G in the row I am currently working on for a credit note, then lock columns I & J in the row I am currently in.

Any Ideas how this can be done? Also can locking and unlocking of cells be done in VBA while the sheet is protected or would the macro need to temporarily unprotect sheet, lock the cells then protect sheet again?

Thanks in advance for any help offered.

PS: I have uploaded a Jpeg of what my conditional formatting does to try and illustrate what it is I need.

Sorry to be a pain but is there a possibility that this can be acheived?

Thanks.

Your code would need to Unprotect, work then re-protect unless you Protect with UserInterface. See the examples in the VBA section of www.excel-it.com. If you need help attach an example workbook.

Thanks for your help guys I appreciate your input.

@suhas.nehete

Thanks for attaching that sample spreadsheet I shall see if I can adapt that to my worksheet it looks like the sort of thing I am after.

Thanks again guys I shall let you know if this works or not.

I think your spreadsheet desdign is amking work for you. Why not have one column in which you enter Credit Note or Invoice then you can use AutoFilter or a PivotTable to see the individual totals for each item.

Hi Guys,

I tried your code suhas.nehete and it works great but I need it be dynamic to so that it will work in every row that has data.

@RoyUK

I hear what you are saying but this spreadsheet is just simply a record to check what credit notes and invoices that I have received the calculations are just simple I don't really need a Pivot Table as it was only ever meant to be a log really.

I have attached the spreadsheet, maybe if you have some spare time could you look at it for me and let me know if the code provided here can be modified to tend to my needs.

Basically in a nutshell this is what I need. if Column C in any row become populated then cells I & J in that row become locked, likewise if column I become populated then cells G & H in that same row are locked.

How can I acheive this and make the macro work automatically so that it locks the cell after data has been entered into it?

I ammended it as below but my vba skills are minimal.

Please Login or Register  to view this content.

The worksheet is Password protected.

@royUK

Sorry about that the password is the same one as in the code I provided which is "open".

Once again sorry for forgetting to include that information in my previous post.

Try this, I haven't got time to break the password. Post back if it needs amending

Please Login or Register  to view this content.

Hi royUK thanks for the code.

The Password was "open" (minus the quotes)

Thanks again I shall check the sheet.

PS: I have uploaded the file this time minus the password.

EDIT: Just tested the code and it does not appear to work for me, maybe I did it wrong.

I entered it as a module and then I clicked on the name tab of the worksheet and clicked 'View Code' and entered it that way but it still does not work. I am at a loss.

Sorry guys, any further info that may help my cause?

Thanks in advance.

@jameslou

Thank you for your help and input with my problem. I did try your sheet and even if I press Enter the code does not work for me and the cells are not locked. Also I cannot check the code because it is password protected.

Any other suggestions guys?

Originally Posted by jameslou

Zyphoon,

unprotect the sheet first then protect it, uncheck the select locked cells, it would work just fine.

Sorry for the way late response but I forgot about this thread. Yes pressing Enter does do the trick and locks the unwanted cells fine.

I take it there is no way to make this work when pressing the TAB key?

Thanks again for your help.