Excel VBA Query

DHPBilcare
DHPBilcare used Ask the Experts™
on
We have a spread sheet in which after a cell has had data entered into it the cell is protected and a password is required to make any further changes.

This is achieved by the following Macro.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Object
    For Each cell In Target.Cells
            ActiveSheet.Unprotect "qaupdate"
            cell.Locked = True
            ActiveSheet.Protect "qaupdate", DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingColumns:=True, AllowFormattingRows:=True
    Next cell
End Sub

This locking by cell is proving too restrictive on the normal data user and they have asked if there is a way to lock down by Row.  i.e. as soon as you go to another line the line that was in use locks for normal use, which allows the initial entry to be corrected by the original person if they spot an error in a cell prior to completing the entire line.

Hope this makes sense.  How could this be accomplished?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
I don't know for prior versions, but when in Excel 2007 you might have a look at this:

    sheet(1).Protection.AllowEditRanges.Add _
        Title:="Classified", _
        Range:=Range("A1:A4"), _
        Password:=wksPassword

it allows a range of cells to be protected, and therefor an entire row aswell.
Commented:
the best way I can see to handle this is to use VBA to mark when you enter a new row then after you leave the row to lock all the cells (if not already locked).
Is that what you're looking for?

Or are you looking to unlock cells when a particular row is accessed (then lock when done)?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial