Automatically locking cells following data entry

  • Thread starter Thread starter Brian Larkin
  • Start date Start date
B

Brian Larkin

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
 
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.
 
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
 
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
 
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
 
Back
Top