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!!
"Dave Peterson" wrote:
> 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.
>
> BKM wrote:
> >
> > 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??
>
> --
>
> Dave Peterson
>
|
Similar Threads
