after cell update

RhodEvans

Board Regular
Joined
Oct 31, 2012
Messages
88
Morning everyone,

I have a workbook that is going to be used by someone who has a (deserved) reputation for wrecking data. I am trying to restrict her access to the sheet, so she can enter but not ruin things.
I have set it up so that the restrictions only apply to her network log-on (using this function)

Code:
 Function isPermitted() As Boolean
If UBound(Filter(Application.Transpose(Sheets("MySecretSheet").Range("A1:A10").Value), Environ("username"))) = 0 Then isPermitted = True

 End Function

and then the protection after:

Code:
If Not isPermitted() Then


The bit I am having issues with is that I want if a cell has data is (I have text, dates, integers and functions) then if she types anything into the cell it will just replace it with the value it had previously. I have a code that works, but have no way to trigger it as there is no afterchange event, and I cannot figure out how to do it. The code I have is below:

Code:
Dim p As Variant


p = ActiveCell.Value

    If ActiveCell.Value = "" Then
        GoTo ender
    Else
     ' ActiveCell.Offset(0, 100).Select
       ActiveCell.Value = p
    End If
ender:

I would be grateful if anyone has any ideas on how I can get this to work, or anything else that would give me the same effect.

As always I am thankful for your help and support.

Rhod
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
How about in the sheet's code module

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,108,501
Messages
5,523,293
Members
409,508
Latest member
Afc

This Week's Hot Topics

Top