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

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

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

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 -