Locking a row

[ewan1979]

Hi All,

I am trying to lock a row once data has been entered so the user can't go in and re -edit it. It forms part of an audit trail so once the user has submitted it that is it.

The user should still be able to enter further rows thereafter but the rows of data already submitted will still be locked.

So for example the user will enter three rows then submit them. The user the next day can go in and view rows 1 to 3, but not edit them, but then be able to enter a new record on row 4.

Is this posssible as I am going round in circles.


Thanks,

Please Login or Register  to view this content.

[ewan1979]

Hi,

Has anyone any ideas or know if this is actually possible?

[snb]

If you want to 'guide' a user, use a 'userform'.
If you want to restrict a user you'd better not let her/him enter a worksheet at all.

[Whizbang]

You could use the Worksheet Change event to test if the row for the Target is already complete. If so, then Undo.

Please Login or Register  to view this content.

The code below will handle changes to ranges with multiple rows:

Please Login or Register  to view this content.

[Whizbang]

I was playing with this some more and noticed some odd behavior with my last code.

This code will undo the changes made, evaluate the ranges to determine if edits should be allowed, and then, if so, undo the undo (essentially accepting the change).

The problem is that, because of the undo, the Tab behavior is a little off. If the change is accepted, the user would expect the next cell to be selected (because they pressed Tab), but the Undo(s) leave the changed cell selected.

Please Login or Register  to view this content.

[Whizbang]

Ok. Fixed the Tab behavior issue. What I did is record the selected cell before I undid the changes. Then just selected it again after the changes were accepted.

Please Login or Register  to view this content.