disallow edit in used rows on save

  • Thread starter Thread starter erin
  • Start date Start date
E

erin

Hello,
I'm trying to prevent users from changing data once it is
entered and saved. It's a document they will add to
repeatedly. Number of rows used each session is
variable. If a row is used and needs to disallow future
editing, there will be text in colA.

Sub protectinput()
Dim myRng As Range

With ActiveSheet
Set myRng = .Range("A:A")
If CellType = "Text" Then
????????

Don't know where to go from there. Since data does need
to be added, it doesn't make sense to protect the sheet.
or does it?? Maybe protect the whole sheet on save, then
on open unlock range defined as blank cell in colA? Not
sure how to do that either, although, willing to try if
any of you pros out there think it's workable.

Thanks in advance!
 
Erin,

This solution assumes you want to protect "Sheet1".

Select all the cells on Sheet1, then format them "unlocked" (uncheck
"locked" on the protection tab), and then protect the sheet without a
password.

Copy the code below, go into the VBE, and paste the code into the
Thisworkbook object's code module.

The code will protect the cells of all rows that have values (not formulas)
entered in column A.

You can modify the code to use a password if you fear that a user might edit
something incorrectly, but you can't reliably hide the password from users,
since it would need to be used in the code. Protecting the code will help,
but isn't foolproof.

HTH,
Bernie
MS Excel MVP

Private Sub Workbook_BeforeSave( _
ByVal SaveAsUI As Boolean, Cancel As Boolean)
Worksheets("Sheet1").Unprotect
Worksheets("Sheet1").Range("A:A").SpecialCells(xlCellTypeConstants). _
EntireRow.Locked = True
Worksheets("Sheet1").Protect
End Sub
 
Back
Top