Macro for locking cells after worksheet is protected

[Guest]

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.

 

[Guest]

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

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

 

[crferguson]

If I remember right, before you can manipulate locking and unlocking
cells, your sheet has to be unprotected. So, to do what you describe,
you'd have to unprotect the sheet, lock the cell, then protect the
sheet.

 

[Guest]

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

 

[crferguson]

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.