Edit Excel Cell or Row only ONCE!

March 13, 2009 at 06:10:44
Specs: Windows 2000
Is it possible to have it so that Excel cells/rows/sheets can be edited only once? That is, information can be entered, then the spreadsheet saved, but subsequent editing of the previously entered information is prohibited? It must be possible to added new information empty cells.

Thanks.


See More: Edit Excel Cell or Row only ONCE!

Report •


#1
March 13, 2009 at 07:53:19
This isn't perfect, but it's a start...

Select the range of cells that the users will be editing (in my example, I use A1:A5).

Format...Cells...Protection tab...remove the Locked checkmark

Protect the worksheet

Paste this code into the ThisWorkbook module of the VBA editor.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 ActiveSheet.Protect Contents:=False
  For Each cell In Range("A1:A5")
   If cell <> "" Then cell.Locked = True
  Next
 ActiveSheet.Protect Contents:=True
End Sub

The user will be able to edit the cells in the range until he saves the workbook. When he saves the workbook, the code will unprotect the sheet, lock the cells within the specified range that contain data and then re-protect the sheet.

Obviously this should be made more robust by using a password (which must be included in the code), choosing the correct options in the Protection dialog box, etc. You should also password protect the VBA code so users can't change it. Macros also have to be enabled on the user's machine or it won't work.

As you can see, it can be done, but it gets complicated when you try to make it idiot - er, I mean, user-proof.


Report •

#2
March 19, 2009 at 05:00:11
Thanks. That's looks to be doing the job. How do I set this for all cells in the workbook/sheet? You have specified A:1 to A:5 as your example. Can I use a wildcard? I'm not an Excel expert by any means! I've tried *, $ and ?.

Report •

#3
March 19, 2009 at 05:27:47
I don't think you want to do this for every cell in the workbbook. To ask VBA to check every cell in the workbook is going to cause the Save operation to take a very long time.

Try it for yourself:

For Each cell In Worksheets("Sheet1").Cells

The best I can offer is to pick a reasonably sized range, a range big enough that the users will never enter data outside of it, but not so big that it causes major Save delays.


Report •

Related Solutions

#4
March 19, 2009 at 06:00:33
BTW, there may be other options for you to use:

You can allow a user to edit a cell and then lock it immediately, which would eliminate the save delay, but not allow for typos or other user-errors.

You could also pop up a message box asking the user to verify his entry before locking the cell.

Both of these methods have their advantages and disadvantages.


Report •

#5
March 24, 2009 at 04:43:51
Thanks very much for you help.

This is working well, up to a point. Without a password users can simply Unprotect Sheet to bypass the code. If I set a password on the protection, users are prompted for this password before the save, which defeats the purpose of the protection.


Report •

#6
March 24, 2009 at 07:14:23
I addressed this specific issue in my original response:

Obviously this should be made more robust by using a password (which must be included in the code), choosing the correct options in the Protection dialog box, etc. You should also password protect the VBA code so users can't change it.

Step 1 - Look up the Protect and Unprotect methods in VBA help to see how to include a password in the code.

Step 2 - In the VBA editor use Tool...VBAProject Properties to set a password so the users can't view the code. I believe you must save, close, and then reopen the workbook before the VBA password will take effect.


Report •


Ask Question