Allow only one value in range.

[Michael]

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

 

[Lars-Åke Aspelin]

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

 

[OssieMac]

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.

 

[Michael]

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










- 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

 

[OssieMac]

Hi Michael,

Try the following. There was no test for additional values equal to input
value. Also inputval was dimensioned then inputvalue used in the code. If you
include Option Explicit at the very top of the VBA editor page and then
before running the code, Select Debug -> Compile. The variables not properly
dimensioned will be highlighted before running the code.

The On Error should be used with Application.EnableEvents = False. If you
get an error in the code and it does not complete running to
Application.EnableEvents = True then events remain turned off until you
either turn them on with code or restart Excel.

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

inputvalue = Target.Value
Set isec = Application.Intersect _
(Range("eventRngStable"), Target)

On Error GoTo ReEnableEvents
Application.EnableEvents = False
If isec Is Nothing Then
MsgBox "out of range"
Else

If WorksheetFunction.CountIf _
(Range("eventRngStable"), _
inputvalue) > 1 Then

Range("eventRngStable").ClearContents
Target.Value = inputvalue
End If

End If

ReEnableEvents:
Application.EnableEvents = True

End Sub