Macro to Lock entire row after input

[turkeyman26]

I am working on a spreadsheet that users fill in every hour. I have formulas for putting time stamps in column "B" when they add their initials to to column "A". They need to fill in other information in the row before they add their initials. Once they put their initials in then the entire row locks. I have searched everywhere but cannot find out how to do the entire row.

Thank you.

 

[Upex]

Turkey,

would using the sheet protection and cell properties work for you?

i.e. all rows baring heading etc unlocked, user fills in the details, and when they do the initials, use code to change the cell properties of the whole row thats been entered to locked, then protect the sheet. The locked cells would then be locked, i.e. headings and the row just completed, but the empty rows would be unlocked still, till the user fills in the initals etc.

 

[turkeyman26]

Upex,

I thought of doing that but I have formulas in different columns. I have to keep those cells always locked so the person cannot delete them by accident. The people filling these sheets out have little to no experience with excel so I need to overprotect them so they cannot delete items. The purpose of the locking of the row is so that they cannot go back in and alter data.

Thank you.

 

[Upex]

So if you set the cells of the relevant columns (with formula in) to all be locked from the start, and your headers etc also locked, and only the cells that the user enters data to be unlocked, then with the protection activated, the user can only add data to empty cells that are unlocked, can't touch the formula etc.

When initals are entered, that row of cells is then locked, this preventing changes to what they have put, they would only be able to enter anything in the next row, which has the combination of locked and unlocked cells in in (unlocked for user entry and locked for formula columns).

Hope you can see what I'm getting at.

Simple example (as can't post up anything as on a tablet)

Column headers in A1:D1

Formula columns B:C

User columns A & D (baring headers)

Header cells a1:a4 locked.

Cells A2:A50000 (whatever range you like) are unlocked.

B2:C50000 locked.

D2:D50000 unlocked.

So on first user entry, they write in a2 and then put initals in d2, which then triggers code to lock cells of row 2 (a2 and d2 then become locked and thus result achieved).

on second user entry, they write in a3 and then put initals in d3, which then triggers code to lock cells of row 3 (a3 and d3 then become locked), rinse and repeat, 1 row at a time.

Obviously the code can check cells are not blank etc and any other validation needed before locking, so they can't lock a blank row by entering initials first etc.

If this doesn't suit your needs etc, then perhaps use a userform to capture the data and it stamps it to a totally locked out sheet?

Upex