How about not selecting the cell at all, but simply locking it?

Rather than:

ActiveCell.Offset(-1, 0).Select
ActiveCell.Locked = True

Just use:
Activecell.Offset(-1,0).Locked=True


To my knowledge, you can't run a macro while still typing in a cell, so
no matter what the user will either have to hit Enter before the macro
can be run.

If the above doesn't work, please explain a little more detail on the
big picture of what you're trying to do. Perhaps there's an easier way
rather than Ctrl+Q every time the user types something in a cell, like
a one-time macro that will lock every cell that's been typed in or
something.

Hugo wrote:
Great!!!!

Just one more thing to make my day perfect..

At the moment, the user has to press "Enter" and then Ctrl+Q (to run macro)==

ActiveSheet.Unprotect Password:="test"
ActiveCell.Offset(-1, 0).Select
ActiveCell.Locked = True
ActiveSheet.Protect Password:="test"
Save

So the active cell goes back up to lock the correct cell. Is there a way to
select the cell after the user types without having to press enter?

Many, many thanks....

Hugo
"Tom Ogilvy" wrote:

The worksheet should be unprotected when you change the locked property.

Activesheet.Unprotect Password:="ABC"
ActiveCell.Locked = True
Activesheet.Protect Password:="ABC"


--
Regards,
Tom Ogilvy


"Hugo" wrote:

I have set up a sheet with some protected and unprotected cells. I then
created a macro to protect cells after user makes an entry. The macro works
ok at this point. I then protect the sheet and the macro does not work.
It gives me error 1004.
I am using ActiveCell.Locked = True and I have tried various combinations
with other functions.
I have also allowed macros to run.