Only allow 1 cell in range to be populated

[brumby]

Hiya,

I am using datavalidation in a range "a3:a70" to only allow a "*" to be entered, this is a selection method to return a series of data in another part of the sheet.

Is is possible if the user enters a * in that range, the rest of the range is cleared, meaning only 1 entry will ever be made?

So........in previous saved version, cell a9 had the *.

User opens workbook and selects a45, this triggers a clear from A3:a70, leaving only a45 with *.


Many Thanks

 

[MickG]

Can you do it with a Change Event as below:-

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Rng As Range
Set Rng = Range("A3:A70")
If Not Intersect(Target, Rng) Is Nothing Then
    Rng.ClearContents: Target = "*"
End If
End Sub

 

[brumby]

superb, thats great, is it possible to only have that range as selectable area? eg rest of sheet not "clickable'

 

[MickG]

You could protect the rest of the sheet:-
http://www.k2e.com/tech-update/tips/155-protecting-specific-cells-in-an-excel-worksheet