Locking and Unlocking Cells via VBA

A

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
 
A

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
 
A

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
 
A

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 -
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top