Macro for locking cells after worksheet is protected

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.

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.

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.