VBA to lock a row based on the value of a cell (worksheet has some locked cells)

Hi,

I have a spreadseet that records people's leave. The people using them are staff and their managers. The staff are only able to complete certain cells (the rest are locked with a password) for obvious reasons.

When someone puts in a leave request the manager needs to go into the file and give their decison using a drop down list in a specified colunmn. I have added a column to the left of this one so that I can add a maker when the manager has selected their decision (options are Blank, Approved, Rejected, Cancelled). This column places an X in the cell if the decision column has an entry (if it is blank an X won't appear). I'm not sure if this is really necessary but it seemed an easy way to identify which rows should be locked.

What I need to do is lock the row once the manager has made their decision so that staff cannot go back in a change the dates etc which at the moment they could do.

The issue I am having is mainly with the password proection that the workbook must have.

1) I need something that will allow managers to have their own passwords i.e. I can't put the password in the code as it is not a constant.
2) The code I have found so far seems to lock the row but it password protects once you make a decision so if a manger has another row they need to give a decision for they have to unprotect again each time so it seems really clunky!

Any help would be greatly appreciated!

Why is it a problem having the password in the code? Password protect the VBAProject.

I usually use set Protect for each sheet in the Workbook's Open event. Use the parameter UserInterfaceOnly:=False so that code will not need to protect and unprotect the sheet. Then, use something like Environ("username") to check if an authorized user could do things in the code like locking or unlocking cells, rows, etc.

Hi,

Maybe it isn't a problem but I am very new to coding. Let me try to explain again. I have a spreadsheet that is being used to book leave. There are certain cells on the worksheet that are open to the user e.g. Name, Contract type etc. Further down is the area where they request leave. So in column B is the date from (where they have to enter the date in a certain format and column C/D (these are merged due to formatting of the worksheet) contains the date to etc. At the end in column AP there is a column for the manager to make their decision (this is protected from the user like most of the other cells.)

What I need is, once the manager has made a decision i.e. they have selected either "Accepted", "Rejected" or "Cancelled" from the drop down list and protected the workbook again, the row where the decision has been made to be locked. Or more specifically the cells that the person could once amend e.g. with the dates etc, is made no longer editable. This will hopefully prevent someone from changing the dates after they have had it approved.

I have looked on various forums/web sites but cannot find the specific code for my purpose and I did wonder if the merged cells (as mentioned above) were causing part of the issue?! Any help is greatly appreciated!

Thanks
D