Avatar of DHPBilcare
DHPBilcareFlag for United Kingdom of Great Britain and Northern Ireland

asked on 

Excel VBA Query

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?
Microsoft ExcelVB Script

Avatar of undefined
Last Comment
rspahitz
ASKER CERTIFIED SOLUTION
Avatar of CvD
CvD
Flag of Netherlands image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
SOLUTION
Avatar of rspahitz
rspahitz
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Microsoft Excel
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo