in cell drop down validation

[stuck]

I can set up an in-cell drop down list that allows a users to pick an entry, e.g.
not set
apples
pears
oranges
bananas

but how do I 'lock' that list so that only the text in the list are the only valid cell entries?

At the moment I can pick an item but then I can delete it, leaving the cell empty. I've tried unticking the 'ignore blanks' in the data validation dialog but it doesn't seem to do anything.

Thanks,

Ken

[HansV]

Data Validation cannot prevent clearing a cell. It requires VBA:
- Right-click the sheet tab.
- Select 'View Code' from the context menu.
- Copy the following code into the worksheet module:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Const TheCell = "A1"
    If Not Intersect(Range(TheCell), Target) Is Nothing Then
        If Target.Value = "" Then
            MsgBox "Don't leave " & TheCell & " empty!", vbExclamation
            Application.EnableEvents = False
            Application.Undo
            Application.EnableEvents = True
        End If
    End If
End Sub

- Change "A1" to the relevant cell address.

Remarks:
- Save the workbook in a macro-enabled format.
- Make sure that users enable macros when they open the workbook.
- If you want to apply this to multiple cells, it becomes more complicated.

[stuck]

Thanks for the clarification Hans.

The Lounge Magic kicked in after I posted and I remembered about Form/ActiveX controls. They might provide me with an alternative solution. I'll have a poke around.

Ken