Locking and Unlocking Cells via VBA

[andreashermle]

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

 

[AB]

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).Locked =
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

 

[andreashermle]

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).Locked =
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

Dear AB:

works like a charm. Exactly what I wanted. Thank you very much for
your superb help.

Regards, Andreas

 

[AB]

No worries - thanks for the feedback!

A.

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 -