Lock cell after first edit on shared workbook

June 19, 2009 at 04:53:18
Specs: Windows XP
Is it possible to have it so that Excel
cells/rows/sheets can be edited only once on
shared workbook ? 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.

I have used below macro it's work find for me
but when i share workbook is giving error

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


See More: Lock cell after first edit on shared workbook

Report •

June 19, 2009 at 06:28:44
If you read the "Features that are unavailable in shared workbooks" in Excel Help you'll see this entry:

Protect or unprotect worksheets or the workbook

In order to protect or unprotect a workbook - either manually or with VBA - you have to unshare the workbook, perform the protection steps and then reshare it. Read the Help files on sharing so that you know the implications of unsharing a workbook when multiple users have it open.

There is code to do this, but there could be issues if other people are editing the workbook at the same time.

This thread shows some code to share/unshare a workbook via VBA.


Report •

June 19, 2009 at 06:38:48
Thanks for Reply,

I am not able to understand your point, i just want to lock cells
after first time edit on my shared work book.

Report •

June 19, 2009 at 08:16:16
If you don't understand my point, then perhaps you don't understand the code you posted above.

If you don't understand the code you posted above, then perhaps you should not be running it in your workbook.

If you don't know exactly what it is doing, how do you know that it's not doing something harmful to your machines?

I'll break it down for you, although all of this information can be gleaned from the Excel Help files.

Locked Cells and Protected Workbooks

1 - Locking cells has no effect unless the workbook is Protected.

2 - If you Unlock all the cells in your workbook, users can enter data even if the workbook is Protected.

3 - You can not Lock or Unlock cells with the workbook Protected. It must be Unprotected to change this setting.

Result: Cells must be Locked and the workbook must be Protected in order to prevent users from entering or changing data in the Locked cells.

The Code

- The Before_Save code posted above Unprotects the workbook, Locks the cells with data in them and then Protects the workbook again.

Result: Cells with data in them cannot be altered once the code Locks them and Protects the workbook.

Limitations of Shared Workbooks

- As per the Help files, the ability to Protect and Unprotect shared workbooks does not exist. The workbook must be unshared before the Protection can be enabled or disabled.

Result: When the Before_Save code posted above tries to Unprotect the shared workbook, it fails because neither the user nor any VBA code is allowed to Protect or Unprotect a shared workbook.


Putting this all together, the bottom line is this:

If you want to prevent data entry you have to both Lock the cell and Protect the workbook. Since you can't Lock a cell while the workbook is Protected, you have to Unprotect the workbook first. The problem is that you can not Unprotect a workbook while it is set to Shared; therefore you have to remove the Sharing first.

This can all be done via code, but it is going to cause problems if other users have the workbook open at that time because of what happens if you try to Unshare a workbook that multiple users are editing.

Report •

Related Solutions

June 19, 2009 at 21:41:35
Thanks for detail description.

Report •

June 20, 2009 at 19:05:23
P.S. Unless you have also hidden and password protected your VBA code, you have left your sheet-protection password visible to anyone who looks at the code. "ash"

On the flip side, if the workbook is shared, the code is not visible to anyone since VBA code is not visible in a shared workbook.

Of course, as you already know, if the workbook is shared, the code won't work.

Interesting Catch 22 you have there!

Report •

Ask Question