Do-Over When cell changes, highlight first empty row and record change

[MDW12]

Re-post with updates. Apologies

I'm creating a log sheet for checking characteristics of items. I'd like to create a way to automatically record when the characteristic spec changes by highlighting the next empty row and recording the change into my last used cell in that row.

So it would do something like this;

Current spec is located in cell D7 and is 6.6"
When D7 is changed to 7.7", look for first blank cell in column A (let's say it is A16)
Highlight A16:F16 and in F16 record the change "6.6" changed to 7.7""

However D7 mentioned above is on sheet 2 and is a reference to cell A9 on sheet 1.

Please see the attached example

Thanks for any help!

[Mumps1]

At present, the value in D7 on sheet 2 is returned by a formula referring to A9 of sheet1. Therefore, D7 will change only when A9 changes. The problem with automatically doing what you asked is that it would require a Worksheet_Change macro and this type of macro is not activated when a change to a cell is the result of a formula. It would work if you reversed the formula, placing it in A9 and having it refer to D7. In this manner, you would manually change D7 triggering the macro and A9 would update according to the formula. Would it work for you if you reversed the location of the formula?

[MDW12]

Unfortunately I cannot do that. I have to have the headers in sheet 2 populate from sheet 1. Could the macro be placed in sheet 1 and start on the change of A9?

[Mumps1]

You are exactly right. Copy and paste the following macros into the worksheet code module. Do the following: right click the tab for your "Before 1" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make your change in A9.

Please Login or Register  to view this content.

[MDW12]

This is great! Just one question: Is there any way to edit this so that say if I change A9 twice or more on sheet 1 before making another entry to the log on sheet 2 that the macro would just overwrite what it previously recorded in column F sheet 2?

My concern is that someone would accidentally type the wrong change into cell A9 sheet 1, press enter, then immediately correct the mistake, and now sheet 2 recorded that the spec changed twice due to the error.

Also, these sheets will be protected. How do I adjust for that?

[Mumps1]

That is possible but it would mean that you would only have the most recent change in sheet 2. You wouldn't have a running record of all changes. Is this what you want?

[MDW12]

No I'd rather have it as is. I can always manually fix it if necessary.

[Mumps1]

Sounds good.

[MDW12]

I'm am still struggling about the protection of the sheets. Not sure where I need to add in the unprotect and protect lines. Thanks!

[Mumps1]

Do you want to protect all the sheets or just some of them? Do you want to use a password to protect them? Also you will have to decide which cells on the protected sheets you want to access so you can enter or modify data in them.

[MDW12]

I would like all of the sheets to be password protected. I have identified which cells I want to allow access to by changing their protection to Unlocked.

[Mumps1]

Start by manually protecting your sheets with the password and then try the following:

Please Login or Register  to view this content.

[MDW12]

This looks like it's working perfectly! Thank you very much Mumps1 I appreciate the help!

[Mumps1]

You are very welcome.

[MDW12]

Could anyone help me expand this to include the same thing for the second part characteristic? I thought I would be able to figure this out myself but haven't been able to.

[Mumps1]

By "second part characteristic" do you mean "Punch Hole Location" as in your sample file and possibly more characteristics in column A? Please explain in detail.

[MDW12]

Correct, that's exactly what I mean. I have hundreds of workbooks like this with varying numbers of characteristics that would be in column A. My goal is do this for each of them.

[Mumps1]

Try:

Please Login or Register  to view this content.

[MDW12]

That's very close but with this code it still changes D7 on sheet 2 instead of just the related cell(E7) when punch hole location is edited

[Mumps1]

I didn't realize that you had formulas in D7 and E7 on sheet 2. Make sure that the formulas in those two cells are still there and try the following:

Please Login or Register  to view this content.

By the way, it looks like there can be only 2 characteristics, length and location. Is this true?

[MDW12]

This looks to be working very well!
For this part there are only 2 characteristics, however, for most of the other parts I have workbooks for there are many more characteristics.(color, diameters, thickness, correct components, profile, etc.)

[Mumps1]

Glad it's working for you.