How do I go about protecting a row of data automatically once it has been confirmed as accurate? I'll explain a bit of what I'm looking for. Several users currently enter data to a shared spreadsheet. That data is verified in accounting and once done so the row is marked with an "x" to indicate agreement and posting to the GL. How would I get Excel to automatically password protect these rows marked with the "x" upon exiting the file. Any suggestions?? I don't want to manually select rows. Thanks.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
How to protect a row of data once information is confirmed
- Thread starter SCOTLAND
- Start date
DRJ
MrExcel MVP
- Joined
- Feb 17, 2002
- Messages
- 3,853
Hi - Welcome to the board
Try this before save code. You can do it for before close, but you would have to save to make sure the changes were kept.
Try this before save code. You can do it for before close, but you would have to save to make sure the changes were kept.
Code:
Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim x As Long
Dim y As Long
Sheets("Sheet1").Unprotect Password:="Password"
y = Sheets("Sheet1").Range("A65536").End(xlUp).Row
For x = 1 To y
If Range("A" & x).Value = "x" Then Range("A" & x).EntireRow.Locked = True
Next x
Sheets("Sheet1").Protect Password:="Password"
End Sub
Upvote
0
erik.van.geit
MrExcel MVP
- Joined
- Feb 1, 2003
- Messages
- 17,832
Re: How to protect a row of data once information is confirm
there must be data in column A, else change the code
this code locks the last row and if you want unlocks the following row
so there will only be one row where data can be put in
regards,
Erik
there must be data in column A, else change the code
this code locks the last row and if you want unlocks the following row
so there will only be one row where data can be put in
Code:
Sub protectlastrow()
ActiveSheet.Unprotect
Range("A65536").End(xlUp).EntireRow.Locked = True
Range("A65536").End(xlUp).Offset(1, 0).EntireRow.Locked = False
ActiveSheet.Protect
End SubErik
Upvote
0
Similar threads
- Question
- Solved
