Determine which cell was populated

[Jock]

When a user types a number into a cell, there are 3 ways to move from that
cell, essentially: <tab>, <enter> or click somewhere with the mouse.
As soon as the cell has lost focus, a user form appears allowing the user to
add other data into text boxes which needs to be copied to the cell to the
right of the one with the number in it.
OFFSET() would work but, as virtually any cell could have focus, this isn't
practical.
I need to identify somehow, which cell the number was entered into (will
always be column D) and copy from User form to cell E on the same row.
There may be gaps in column D which is why I can't use the .End(xlUp)
command as the last entry may not be the one just entered.
All help appreciated.

 

[Barb Reinhardt]

Try setting a public range variable that's updated during a Worksheet_Change
event and go from there.

HTH,
Barb Reinhardt

 

[Joel]

What yo ucan do is use application.undo, find the active cell, then put the
data back into the cell using application.redo. It is ugly but it works.

 

[Dave Peterson]

If you tie into the worksheet_Change event, then you can use Target to show you
the range that's been changed.

I'd put this into a General Module (not in the worksheet module, not in the
userform module):

Option Explicit
Public WhichCell As Range


Then this would go in the worksheet module that needs the behavior:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then
Exit Sub 'single cell at a time
End If

If Intersect(Target, Me.Range("D")) Is Nothing Then
Exit Sub 'change not in column D
End If

Set WhichCell = Target
UserForm1.Show
Set WhichCell = Nothing
End Sub

And finally, in the userform module:

Option Explicit
Private Sub UserForm_Initialize()
If WhichCell Is Nothing Then
'not called from the worksheet_Change event
Else
'just to show that it works.
MsgBox WhichCell.Address(external:=True)
End If
End Sub