Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
disallow edit in used rows on save
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
disallow edit in used rows on save
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(xlC ellTypeConstants). _ EntireRow.Locked = True Worksheets("Sheet1").Protect End Sub "erin" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Disallow File Save if No Input in Cells? | Excel Discussion (Misc queries) | |||
Disallow "SAVE" only "SAVE AS" or prompt | Setting up and Configuration of Excel | |||
cannot edit and save | New Users to Excel | |||
cannot edit and save | Excel Discussion (Misc queries) | |||
cannot edit and save | Excel Discussion (Misc queries) |