On Mar 17, 2:52*am, OssieMac
wrote:
Suggest in lieu of following
Range("testrng").Value = ""

use
Range("testrng").ClearContents

That doesn't take out formatting.

There is also ClearFormats and ClearComments and of course Clear which
actuallly means Clear All.

--
Regards,

OssieMac



"Lars-Åke Aspelin" wrote:
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

.- Hide quoted text -

- Show quoted text -

Thanks this was helpful but I am still having a problem. Now it
inserts the value and then clears it eventhough the clear is before
setting the target back to the input value. Am I mssing somethign
stupid?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isec As Range
Dim inputval As Variant

inputval = Target.Value
Set isec = Application.Intersect(Range("eventRngStable"), Target)
Application.EnableEvents = False
If isec Is Nothing Then
MsgBox "out of range"
Else


Range("eventRngStable").ClearContents


End If
Target.Value = inputvalue
Application.EnableEvents = True


End Sub