Allow Input, restrict deletion & Share Workbook

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I saw a solution for "Allowing Input, but restricting deletion" once data is
entered into a cell and it works great (thanks Gord Dibben), however, it does
not work when I set my workbook up for sharing. I need to have a workbook
that many people can access but once the cell is populated, then it cannot be
changed. The event code worked great until I tried to share the workbook. Is
this possible??
 
I would bet that Gord's suggestion unprotected the worksheet, change the locked
property for the newly changed cell to locked and then reprotected the
worksheet.

But since you've shared the workbook, then the protection of the worksheet
change be changed.

This may work for you.

1. Unshare the workbook.
2. Lock the cells that can't be changed
3. Unlock the cells that are ok for data entry
4. Create a new worksheet named Hidden and hide that worksheet
5. Add this code to the worksheet module that should have this behavior (not
the Hidden worksheet)

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim HiddenWks As Worksheet
Dim myArea As Range

Set HiddenWks = Worksheets("Hidden")

On Error GoTo ErrHandler:

If Application.CountA(HiddenWks.Range(Target.Address)) > 0 Then
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
MsgBox "That range has been already been entered--change undone!"
End With
Else
For Each myArea In Target.Areas
HiddenWks.Range(myArea.Address).Value = myArea.Value
Next myArea
End If

ErrHandler:
Application.EnableEvents = True

End Sub

======
It tries to mirror the changes in that hidden sheet. If the user fills in a
cell that's alread got something in the same location, then it does and
Edit|Undo (in code) and changes things back.

Don't allow the users to insert/delete rows or columns when you protect your
real worksheet. It'll screw everything up.
 
Dave,
*You are great, however, I don't know code and it gave me an error and I
don't know how to fix it.
Compile error: Ambiguous name detectd worksheet_change

*Then it goes to the second line in your code:
Private Sub Worksheet_Change(ByVal Target As Range)

I am guessing I need to change something to be relative to my document, but
am not sure what.

Can you help a hopeless Newbie??

Thanks!!
 
Never mind!!! I just realized that I had to delete the code I had entered
per Gord's suggestion. I deleted that and now it works beautifully.

You are a genius!! Thank you!
Barbara
 
You could delete that first
..enableevents = true

It'll be picked up in the errhandler.
 
Back
Top