7

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."

Journeyman Geek
  • 133,878
johnnnieyy
  • 71

5 Answers5

4

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.

rfportilla
  • 602
1

I think this question may have been misinterpreted. If so, and if I'm interpreting it correctly, here is a solution.

Excel actually does allow a spreadsheet user to overwrite a cell which uses a validation list; if the list included the values "Apple", "Peach", and "Orange", standard operation allows a user to type "Broccoli" in the cell if it's unprotected, just as if there was no validation list attached to it. However, protecting the cell and the worksheet disables the capability to select an item from the validation list, and that can be a problem.

If that's the issue, here is a solution:

1.  Format the cell using the validation list so it's 
    unprotected. 
2.  With the cursor positioned at that cell, open the 
    Validation menu origintally used to identify the validation 
    list. 
3.  On the Settings tab of the Data Validation window pane,
    be sure that "ignore blank" is unchecked, and 
    continue to leave that window pane open. 
4.  On the "Error alert" tab of the Data Validation window
    pane:  
      a) Be sure "Show error alert after invalid data is
         entered" is checked. 
      b) Select "Stop" under the "Style" heading.
      c) Give your error alert a name under "Title"; this 
         can be anything, but a short title is best. 
      d) Under "Error message", type a short message that you
         want to appear if a user tries to manually type a value
         in the cell - something like "Please use the drop-down
         menu provided to select a value for this cell."
      e) Click "OK".

That will block people from entering anything they want in a cell that's intended to use data validation, even if the worksheet is unprotected. You may want to protect the worksheet nevertheless, though, to prevent unintended updates to the data validation list itself.

Todd Selle
  • 11
0

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.

Catherine
  • 1
0

Some of the other answers have touched on the problem: You can't protect the cell because then a user can't change its value even with the drop-down and you can't leave it unprotected because them someone can copy-paste the text directly into the cell despite the data validation. However, even though data validation doesn't prevent the pasting of data, it's still running and it still knows the cell doesn't comply with the validation rules. If you're comfortable adding a macro, you can add the code below to any worksheet that needs this kind of protection.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Validation.Value = False Then Application.Undo
End Sub

Alternatively, you can add this macro to ThisWorkbook for it to protect all the worksheets. Be forewarned, though, this will fire on every single change to every cell in every sheet. If you have some automated process that changes many cells at once, this may slow down that process.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Validation.Value = False Then Application.Undo
End Sub

Both of these macros perform the same function: After a change, it evaluates the data validation of the changed cell, if it has any. If that validation is reporting that the cell contents do not meet the requirements, it performs the Undo action to reverse the edit. This only works when the sheet is protected, though. If the sheet is unprotected and someone copy-pastes a cell, it'll overwrite everything including the data validation so there won't be anything to check against any more.

Watch what happens when I try to copy / paste at the end of this video.

GIF

Engineer Toast
  • 5,218
-1

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
Stephen Rauch
  • 3,296
Raja Sekhar
  • 1