How to obtain what the user has just entered (Activecell not working for me)

[Susan Hayes]

Hi again
Im trying to check an area of the worksheet (ie e4:n13) whether the
number entered by the user already exists in that area. Currently my
program uses activecell but, after the user has entered the value for
example, in e5, pressed enter, the activecell becomes e6 and the
program doesnt work. How can you obtain what the user has just
entered in e5. I could move the cell up but the user may tab instead!

Thank you

John

Private Sub Worksheet_Change(ByVal Target As Range)

'Application.EnableEvents = False
'On Error GoTo ws_exit
If Not Intersect(Target, Range("e4:n13")) Is Nothing Then
With Target
''''''''''''''''''''''''''''''''''''''''


Dim myarray(20, 10)
Dim currentlane
Dim checkvalue

' ******* shows me the cell after pressing enter********
Dim rng As Range
Set rng = ActiveCell
Dim generalrng As Range
Cells(35, "d") = rng.Address(0, 0)
' ******************************************************

currentlane = .Value
checkvalue = IsNumeric(currentlane)

'reads the existing area
For i = 1 To 10
For j = 1 To 10
Set generalrng = Cells(i + 3, Chr(68 + j))
If Not rng = generalrng Then
myarray(i, j) = Cells(i + 3, Chr(68 + j))

End If
Next
Next

'performs check using activecell
If checkvalue = True Then 'And currentlane < MAXLANEVALUE Then
For i = 1 To 10
For j = 1 To 10
'Set generalrng = ActiveCell
If Not rng = generalrng Then
If .Value = myarray(i, j) Then
Cells(1, "K") = "duplicate"

Exit Sub
End If
End If
Next
Next

End If

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
End With
End If
'ws_exit:
' Application.EnableEvents = True

End Sub

 

[Jake Marx]

Hi Susan,

The Target parameter will hold a reference to the Range that triggered the
Change event. So if cell E5 is changed, Target will refer to E5. So you
should use Target.Value instead of ActiveCell.Value. However, keep in mind
that multiple cells can change at once (for example, selecting multiple
cells and pressing Delete), so Target can hold a reference to a multi-cell
Range. Depending on what you want to do, you could check Target to make
sure it's a single cell, or you can use Target(1,1) to get the top-left cell
in the Range.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Dave Peterson]

You may want to use Target--that's the cell that's changing.

If I only want to check when one cell is changed, I add this:

if target.cells.count > 1 then exit sub

Near the top.

I'm not quite sure what you're doing, but maybe using a worksheet function would
be sufficient.

if application.countif(me.range("whateverrangeyou'rechecking"),target.value) > 0
then
'maybe > 1 ????
'it's there
end if