Apply formula as users do entry for each row. Excel 2007

[Lifeseeker]

Hi,

I am wondering if a vba is possible to accomplish this little task:

There are three columns, two of which require manual entry. The 3rd one is derived.

I would like to have a vba to apply the formula for each row when users are doing data entry instead of applying formula to the entire column right upfront, which tremendously increase CPU workload at the beginning.

formula is:

Please Login or Register  to view this content.

In addition, I would like to have the formula column(col C) somehow locked so that users don't accidentally override the formula with their "own" values. Is this possible??

I have attached a spreadsheet for reference to test it out.

[Kenneth Hobson]

In VBE, open Project Explorer from the View menu. Doubleclick ThisWorkbook object and paste this and change the password and run or close and open the workbook to activate.

Please Login or Register  to view this content.

Protect Column C of course.

Doubleclick Sheet1 object and paste:

Please Login or Register  to view this content.

[tlafferty]

Select columns A and B, click format, cells, protection, remove the check from locked. Click the review ribbon, then click protect sheet. Paste this code into the code module for Sheet1:

Please Login or Register  to view this content.

[Lifeseeker]

Hi,

Sorry I'm stuck at protecting the worksheet. After I reopen the workbook, it seems that all columns are locked down. Did you mean to specify which column to protect in the workbook_open() code?

I would just like to lock down certain number of columns while still being able to use autofilters later on. Does locking down columns prevent me from using autofilters?

Files reattached.

Thanks

[protonLeah]

Possible solution. It does not enter a formula, just the values.
If column C is selected, it moves to column A of the next row.

[Lifeseeker]

wow thank you.

Could you explain how this part of the code works?

Please Login or Register  to view this content.

UCase is the uppercase function?

[protonLeah]

The "UCASE" is used to convert the contents of the cell to upper case, since Y and y are not the same when comparing. So, then I don't have to check for both upper/lower case entries.

[Lifeseeker]

Oops sorry, it didn't work because I had one of the columns date type set to a different format.

Let me keep on testing.

Ignore this

thanks

[Lifeseeker]

Hi,

This time it's not working properly. I have managed to add two values in there, and syntactically it makes sense, but it's just not running.....

The first value is the original and the second one is a datediff() test.

Could you or anybody help me in anyway if possible?

Thank you

I'm reattaching the file.

[Kenneth Hobson]

IF you change columns to watch for change events, then you must change the event code.

For the protect/unprotect issue, you need to unprotect the sheet by code or manually if you need to change cell protection. In ThisWorkbook, I also added code to protect on open or unprotect if you don't want to do it manually. As before change the value of pw to suit.

Please Login or Register  to view this content.

As before, here is sheet code. Notice how I assigned the column letters to variables so that the next time you change things, you can just change those column letter values.

Please Login or Register  to view this content.

Of course no formula is really needed. If case is not to be considered, UCase() or LCase() can be used as detailed earlier.