Automatically locking cells following data entry
 

I have a workbook that has 25 different worksheets.
The cells are all formatted as locked with the exception
of those that are allowed for data entry. At a point when
all data is added I want to be able to click on a button
that will progress through all worksheets and lock all
cells.

Can anyone please provide me with the code or a macro
that I can assign to a button to do this please.

Thank you in advance.

Brian

Automatically locking cells following data entry
 

Application.ScreenUpdating = False
set sh1 = ActiveSheet
for each sh in ThisWorkbook.Worksheets
sh.Activate
Sh.Unprotect Password:="ABCD"
Cells.locked = True
sh.Protect Password:="ABCD"
Next
Sh1.Activate
Application.ScreenUpdating = True

would be a general approach.

--
Regards,
Tom Ogilvy

"Brian Larkin" wrote in message
...
I have a workbook that has 25 different worksheets.
The cells are all formatted as locked with the exception
of those that are allowed for data entry. At a point when
all data is added I want to be able to click on a button
that will progress through all worksheets and lock all
cells.

Can anyone please provide me with the code or a macro
that I can assign to a button to do this please.

Thank you in advance.

Brian

Automatically locking cells following data entry
 

Have tried the code and am getting the following error
occur:
Runtime error '1004':
Unable to Set the Locked property of the Range Class.

When I open the debug window, the line of code selected
is Cells.Locked = True

any ideas of how to get this working.
I did try and put in an 'on error resume next' which
seemed to stop any error messages and cells were all
locked and passwords reset, but is worrying that there
may be a bug in the vb code?

Brian
-----Original Message-----
Application.ScreenUpdating = False
set sh1 = ActiveSheet
for each sh in ThisWorkbook.Worksheets
sh.Activate
Sh.Unprotect Password:="ABCD"
Cells.locked = True
sh.Protect Password:="ABCD"
Next
Sh1.Activate
Application.ScreenUpdating = True

would be a general approach.

--
Regards,
Tom Ogilvy

"Brian Larkin" wrote in message
...
I have a workbook that has 25 different worksheets.
The cells are all formatted as locked with the
exception
of those that are allowed for data entry. At a point
when
all data is added I want to be able to click on a
button
that will progress through all worksheets and lock all
cells.

Can anyone please provide me with the code or a macro
that I can assign to a button to do this please.

Thank you in advance.

Brian


.

Automatically locking cells following data entry
 

in a general module, the code works fine for me. You might make a slight
change to make it more robust

Application.ScreenUpdating = False
set sh1 = ActiveSheet
for each sh in ThisWorkbook.Worksheets
sh.Activate
Sh.Unprotect Password:="ABCD"
Sh.Cells.locked = True ' <== add Sh.
sh.Protect Password:="ABCD"
Next
Sh1.Activate
Application.ScreenUpdating = True

would be a general approach.

Regards,
Tom Ogilvy


Brian Larkin wrote in message
...
Have tried the code and am getting the following error
occur:
Runtime error '1004':
Unable to Set the Locked property of the Range Class.

When I open the debug window, the line of code selected
is Cells.Locked = True

any ideas of how to get this working.
I did try and put in an 'on error resume next' which
seemed to stop any error messages and cells were all
locked and passwords reset, but is worrying that there
may be a bug in the vb code?

Brian
-----Original Message-----
Application.ScreenUpdating = False
set sh1 = ActiveSheet
for each sh in ThisWorkbook.Worksheets
sh.Activate
Sh.Unprotect Password:="ABCD"
Cells.locked = True
sh.Protect Password:="ABCD"
Next
Sh1.Activate
Application.ScreenUpdating = True

would be a general approach.

--
Regards,
Tom Ogilvy

"Brian Larkin" wrote in message
...
I have a workbook that has 25 different worksheets.
The cells are all formatted as locked with the
exception
of those that are allowed for data entry. At a point
when
all data is added I want to be able to click on a
button
that will progress through all worksheets and lock all
cells.

Can anyone please provide me with the code or a macro
that I can assign to a button to do this please.

Thank you in advance.

Brian


.

Automatically locking cells following data entry
 

Tom
I had already tried your suggestion but I am still
getting the same error message.

For additional info I am using Excel97 SR2.
Have tried this on a new workbook and adding data to a
block of cells in each of three worksheets, setting one
column with six rows as unlocked. Then running the code
by clicking the button. The same error message appears.

Any ideas why?

Brian
-----Original Message-----
in a general module, the code works fine for me. You
might make a slight
change to make it more robust

Application.ScreenUpdating = False
set sh1 = ActiveSheet
for each sh in ThisWorkbook.Worksheets
sh.Activate
Sh.Unprotect Password:="ABCD"
Sh.Cells.locked = True ' <== add Sh.
sh.Protect Password:="ABCD"
Next
Sh1.Activate
Application.ScreenUpdating = True

would be a general approach.

Regards,
Tom Ogilvy


Brian Larkin wrote in message
...
Have tried the code and am getting the following error
occur:
Runtime error '1004':
Unable to Set the Locked property of the Range Class.

When I open the debug window, the line of code selected
is Cells.Locked = True

any ideas of how to get this working.
I did try and put in an 'on error resume next' which
seemed to stop any error messages and cells were all
locked and passwords reset, but is worrying that there
may be a bug in the vb code?

Brian
-----Original Message-----
Application.ScreenUpdating = False
set sh1 = ActiveSheet
for each sh in ThisWorkbook.Worksheets
sh.Activate
Sh.Unprotect Password:="ABCD"
Cells.locked = True
sh.Protect Password:="ABCD"
Next
Sh1.Activate
Application.ScreenUpdating = True

would be a general approach.

--
Regards,
Tom Ogilvy

"Brian Larkin" wrote in message
...
I have a workbook that has 25 different worksheets.
The cells are all formatted as locked with the
exception
of those that are allowed for data entry. At a point
when
all data is added I want to be able to click on a
button
that will progress through all worksheets and lock
all
cells.

Can anyone please provide me with the code or a
macro
that I can assign to a button to do this please.

Thank you in advance.

Brian


.



.