Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locking and Unlocking Cells via VBA
Dear Experts:
I would like to achieve the following on the ACTIVE workseet by applying a VBA code: 1. Lock all the cells of the current worksheet that would be selected by pressing Ctrl+Shift+End 2. De-lock those cells that require data entries by the user, i.e cells where data entry is restricted to certain values (by data validation) Help is much appreciated. Thank you very much in advance. Regards, Andreas |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locking and Unlocking Cells via VBA
Try this one out and post back what needs to be changed if anything:
Sub DoTheLock() Const MyPWD As String = "TypeYourPWDHere" With ActiveSheet 'unprotect you ws .Unprotect MyPWD 'lock used range .UsedRange.Locked = True On Error GoTo ThereAreNoValidations: 'unlock celsl with validation .Cells.SpecialCells(xlCellTypeAllValidation).Locke d = False On Error GoTo 0 'Re-protect the ws .Protect MyPWD End With ExitSub: On Error GoTo 0 Exit Sub ThereAreNoValidations: MsgBox "No cell has a validation!", vbExclamation End Sub On Feb 3, 6:45*am, andreashermle wrote: Dear Experts: I would like to achieve the following on the ACTIVE workseet by applying a VBA code: 1. Lock all the cells of the current worksheet that would be selected by pressing Ctrl+Shift+End 2. De-lock those cells that require data entries by the user, i.e cells where data entry is restricted to certain values (by data validation) Help is much appreciated. Thank you very much in advance. Regards, Andreas |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locking and Unlocking Cells via VBA
On 3 Feb., 10:52, AB wrote:
Try this one out and post back what needs to be changed if anything: Sub DoTheLock() * * Const MyPWD As String = "TypeYourPWDHere" * * With ActiveSheet * * * * 'unprotect you ws * * * * .Unprotect MyPWD * * * * 'lock used range * * * * .UsedRange.Locked = True * * * * On Error GoTo ThereAreNoValidations: * * * * * * 'unlock celsl with validation * * * * * * .Cells.SpecialCells(xlCellTypeAllValidation).Locke d = False * * * * On Error GoTo 0 * * * * 'Re-protect the ws * * * * .Protect MyPWD * * End With ExitSub: * * On Error GoTo 0 * * Exit Sub ThereAreNoValidations: * * MsgBox "No cell has a validation!", vbExclamation End Sub On Feb 3, 6:45*am, andreashermle wrote: Dear Experts: I would like to achieve the following on the ACTIVE workseet by applying a VBA code: 1. Lock all the cells of the current worksheet that would be selected by pressing Ctrl+Shift+End 2. De-lock those cells that require data entries by the user, i.e cells where data entry is restricted to certain values (by data validation) Help is much appreciated. Thank you very much in advance. Regards, Andreas Dear AB: works like a charm. Exactly what I wanted. Thank you very much for your superb help. Regards, Andreas |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locking and Unlocking Cells via VBA
No worries - thanks for the feedback!
A. On Feb 4, 2:37*pm, andreashermle wrote: On 3 Feb., 10:52, AB wrote: Try this one out and post back what needs to be changed if anything: Sub DoTheLock() * * Const MyPWD As String = "TypeYourPWDHere" * * With ActiveSheet * * * * 'unprotect you ws * * * * .Unprotect MyPWD * * * * 'lock used range * * * * .UsedRange.Locked = True * * * * On Error GoTo ThereAreNoValidations: * * * * * * 'unlock celsl with validation * * * * * * .Cells.SpecialCells(xlCellTypeAllValidation).Locke d = False * * * * On Error GoTo 0 * * * * 'Re-protect the ws * * * * .Protect MyPWD * * End With ExitSub: * * On Error GoTo 0 * * Exit Sub ThereAreNoValidations: * * MsgBox "No cell has a validation!", vbExclamation End Sub On Feb 3, 6:45*am, andreashermle wrote: Dear Experts: I would like to achieve the following on the ACTIVE workseet by applying a VBA code: 1. Lock all the cells of the current worksheet that would be selected by pressing Ctrl+Shift+End 2. De-lock those cells that require data entries by the user, i.e cells where data entry is restricted to certain values (by data validation) Help is much appreciated. Thank you very much in advance. Regards, Andreas Dear AB: works like a charm. Exactly what I wanted. Thank you very much for your superb help. Regards, Andreas- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Locking/unlocking cells based on column headers | Excel Programming | |||
Unlocking and Locking in a Marco | Excel Programming | |||
locking and unlocking worksheets | Excel Worksheet Functions | |||
Locking/Unlocking cells upon condition | Excel Discussion (Misc queries) | |||
Locking/unlocking cells | Excel Programming |