Need update to current VBA code to lock a particular cell & not entire sheet

[rtehlan]

Hi,

I am currently using this code assigned to a button which locks my sheet so no one can edit in a workbook but now i would like this code to only lock a particular cell which contains a list dropdown or range for eg. Cell C4 or C4:H4
Can someone please help me on this. Ive tried a lot but to no success.
Will be really thankful to you.

Sub LockSheet()

If ActiveSheet.ProtectContents = True Then
ActiveSheet.Unprotect
MsgBox "The Entity is now unlocked"
Else

ActiveSheet.Protect
MsgBox "The Entity is now locked"

End If


End Sub

 

[cooper645]

This works on Excel 16 for Mac,

Sub LockSpecific()

If ActiveSheet.ProtectContents = True Then
    ActiveSheet.Unprotect
        MsgBox "Sheet Unlocked"
Else
Cells.Locked = False
[COLOR=#ff0000]    Range("C4").Locked = True 'amend to your dropdown[/COLOR]
        ActiveSheet.Protect
            ActiveSheet.EnableSelection = xlUnlockedCells
                MsgBox "Your Dropdown is locked"
End If


End Sub

If the sheet is locked, it unlocks it for you to amend as per your code,

If the sheet is unlocked for you to edit then it sets all cells protection to unlocked, less the one your drop down is in, then locks the sheet allowing the user to only select unlocked cells.

 

[Logit]

.
Works in Excel 2007 / Win 10 as well.

 

[Trebor76]

You could just unprotect the tab and then unselect the Locked option for cell C4 from the Protection tab within the Format Cells dialog. After you then reprotect the tab the users will still be able to use the drop-down but all other cells will still be protected. No code needed (if I've understood correctly).

 

[rtehlan]

WOW. Works like a charm.
I only updated the msg wordings & the range reference since the drop down was in a merged cell 'C4:H4'

Thank you for your time.

 

[rtehlan]

Wanted it the opposite way. The above code worked well.

Thank you

 

[cooper645]

No problem.
Glad to have helped.