On Tue, 16 Mar 2010 14:21:29 -0700 (PDT), Michael
wrote:

I am trying to write a script that has the result of only allowing one
value within a range at any time. So if the user puts new value in the
range the range is cleared except for the new value. The below works
but if the user moves the cursor into the range it gets cleared. I
only want to it clear and use the new value if a new value is entered
in it.

use the the Change event creates a recursive problem where it keeps
changing and repeating.

Any other ideas?


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim inputval As Variant
Dim c As Variant

inputval = Target.Value

'If Target.Row = 3 Then
For Each c In Range("testrng")
If c.Address = Target.Address Then
'MsgBox Target.Value
Range("testrng").Clear
Target.Value = inputval
End If
Next c
End Sub

To avoid recursion problem, disable the event handling while clearing
the range. Try this procedure

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("testrng"), Target) Is Nothing Then
inputval = Target.Value
Application.EnableEvents = False
Range("testrng").Value = ""
Target.Value = inputvalue
Application.EnableEvents = True
End If
End Sub

I changed Range().Clear to Range().Value = "" as clear will also
remove any formatting to the range. Use Clear if that is what you
want.

Hope this helps / Lars-Åke