Protecting a cell, but allowing the drop down list to work

Question

I want to protect a cell that I have a drop down list in, but I still want the drop down list to work.

When I try to protect it, the user is not able to use the drop down list to select other items or macros.

I get this error message

"The cell or chart that you are trying to change is protected and therefore read-only. To modify a protected cell or chart, first remove protection using the Unprotected Sheet command (Review tab, Changes group). You may be prompted for a password."

Answer 1

The dropdown is attached to the cell. That's where it stores the data. The validation will guarantee that the data is valid.

If you haven't already, make sure that the cell is not locked. Right-click the cell and click format cells and then go to the Protection tab. The Locked check box should be unchecked.

Answer 2

On my computer (PC running Excel 2010), the dropdown list itself actually seems to be attached to the cell immediately to the right. So if I want a dropdown list in A7, I have to unlock both A7 and B7.

This may be a bug, but it is a relatively simple fix.

Answer 3

In Protected sheets:

Paste below link in workbook

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim wsh As Variant
    For Each wsh In Worksheets(Array("Sheet1"))
        wsh.EnableOutlining = True
        wsh.Protect UserInterfaceOnly:=True, Password:="", _
            DrawingObjects:=False, _
    Contents:=True, _
    Scenarios:=True, _
    AllowFormattingCells:=False, _
    AllowFormattingColumns:=False, _
    AllowFormattingRows:=False, _
    AllowInsertingColumns:=False, _
    AllowInsertingRows:=False, _
    AllowInsertingHyperlinks:=False, _
    AllowDeletingColumns:=False, _
    AllowDeletingRows:=False, _
    AllowSorting:=False, _
    AllowFiltering:=False, _
    AllowUsingPivotTables:=False
    Next wsh

Dim Oldvalue As String
Dim Newvalue As String

On Error GoTo Exitsub
If Target.Address = "$C$2" Then 'As required
    If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
    Else: If Target.Value = "" Then GoTo Exitsub Else
        Application.EnableEvents = False
        Newvalue = Target.Value
        Application.Undo
        Oldvalue = Target.Value
        If Oldvalue = "" Then
            Target.Value = Newvalue
        Else
            Target.Value = Oldvalue & ", " & Newvalue
        End If
    End If
End If


Exitsub:
Application.EnableEvents = True

End Sub