| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Locking from data entry but allowing oulining to function? | Excel Discussion (Misc queries) | |||
| Locking cells automatically after enter | Excel Discussion (Misc queries) | |||
| auto locking cells after new entry/edit | Setting up and Configuration of Excel | |||
| Locking a cell after data entry (Excel 2003) | Excel Discussion (Misc queries) | |||
| Automatically copy previous cells in data entry in Excel | Excel Discussion (Misc queries) | |||
Linear Mode
