how to protect drop down list

[pishgaman]

Hi everyone.i have a drop down list with validation data.it cant be locked because user cant select from the list,and since it is unlocked user can delete the contents of the selection cell by del key on keyboard.How can i prevent this
issue.Your help would be highly appreciated.

 

[JoeMo]

Welcome to the forum!

You need VBA to do what you have requested. Here's some sheet code for the worksheet that you can use. Change the cell address (in red) to whatever cell you want to protect against users deleting content.

To install the code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("L6")) Is Nothing Then Exit Sub  'change cell address (in red) to be protected here
If Target.Value = "" Then
    MsgBox "Sorry, you cannot delete the content of cell: " & Target.Address & vbLf & vbLf & "Change the content using the dropdown menu"
    With Application
        .EnableEvents = False
        .Undo
        .EnableEvents = True
    End With
End If
End Sub

 

[Superfinn]

Welcome to the forum!

You need VBA to do what you have requested. Here's some sheet code for the worksheet that you can use. Change the cell address (in red) to whatever cell you want to protect against users deleting content.

To install the code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("L6")) Is Nothing Then Exit Sub  'change cell address (in red) to be protected here
If Target.Value = "" Then
    MsgBox "Sorry, you cannot delete the content of cell: " & Target.Address & vbLf & vbLf & "Change the content using the dropdown menu"
    With Application
        .EnableEvents = False
        .Undo
        .EnableEvents = True
    End With
End If
End Sub

This is an excellent code, but how to protect a column from ANY changes apart from drop-down list?
The end user shouldn't be able to change any letters. How to change this code?