Automatically update a protected cell based on contents of an unprotected cell

Hello.

I am trying to find some VBA that will allow protected cells to be updated when a user enters or selects data in other cells.

For example:

Cell 'A1' starts out blank.
Cell 'B1' has a drop down to select specific items.
Cell 'C1' is the date of the order and is a locked cell.
Cell 'D1' is the date of completion and is a locked cell.
The worksheet is protected to prevent direct changes to cells C1 and D1.

If a user enters any information in A1, then C1 displays the current date.
If a user selects 'Complete' from the drop-down list in B1, then D1 displays the current date. Both of these actions are independant of one another.

This format is the same for every cell in the 4 columns indicated above. I thought I was able to do this in another spreadsheet I created a year or so ago but I have not been able to figure out what I did and I do not have the spreadsheet to look at. Any help would be appreciated.

Thanks,
Andrew

These are worksheet_change events. Right-click on the sheet tab and select VIEW CODE, then paste in this code...edit to your needs:

Please Login or Register  to view this content.

This will do what you want, make sure columns A and B are unlocked prior to locking the sheet.

If there is ALREADY data in columns C and D when you make another change to the adjacent A & B cells, the macro will give you the option to LEAVE the date that is in there already.

First issue - different people set up their local machines differently. They may have theirs set to NOT run macros and NOT ask, just don't run them. This stuff only works with macros enabled.

Second issue - The "UserInterfaceOnly" flag is not a setting that is retained when the sheet is closed. It has to be reset.

Change the macro I gave you to include the password, which I didn't really think was necessary. Obviously you need to put in the correct password.

Please Login or Register  to view this content.

Another option would be to set the flag in a WorkBook_Open macro so it's taken care of immediately. Up to you. Of course, since I don't know your password, I can't make further edits on this sheet.

Last Issue - there's no need to do in macro what a worksheet function will. Put a formula in K3 and copy down:

=J3*F3

Then go to TOOLS > OPTIONS > VIEW > [ ] Zero Values and uncheck that flag so this sheet doesn't show the zeros for incomplete rows (optional)