Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine which cell was populated
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. -- Traa Dy Liooar Jock |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine which cell was populated
Try setting a public range variable that's updated during a Worksheet_Change
event and go from there. HTH, Barb Reinhardt "Jock" wrote: 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. -- Traa Dy Liooar Jock |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine which cell was populated
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. "Jock" wrote: 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. -- Traa Dy Liooar Jock |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine which cell was populated
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: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 Jock wrote: 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. -- Traa Dy Liooar Jock -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can i restrict input in a cell until previous cell populated? | Excel Discussion (Misc queries) | |||
First populated cell in row array/ Last populated cell in row arra | Excel Worksheet Functions | |||
Determine if Column Populated | Excel Programming | |||
formula for a cell which is blank returns populated cell | Excel Discussion (Misc queries) | |||
Lat populated cell | Excel Programming |