Address of Cell that lost focus?

[HotRod]

I'm trying to set a default value for a column using VBA and am running code
when the Worksheet_SelectionChange is fired, the problem is that the Target
value is the Value of the cell that just got the focus, how do I get the
address of the cell that just lost focus?

 

[zackb]

Hello there,

Here is an example of some worksheet code which could start you off ...


Option Explicit

Dim rngLast As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo firstRun
MsgBox "Current Address: " & Target.Address(0, 0) & vbCrLf & _
"Last Address: " & rngLast.Address(0, 0)
firstRun:
'must be last
Set rngLast = Target
End Sub


To install this code:

Copy code.
Right click sheet tab (of desired installation).
Select View Code.
Paste on right.
Alt + F8 to return to Excel.
Save before doing anything else.


Note that it will always fail out before the Range Object is set to
anything - as in the first time it is run (or the first time you select
anything/cell - you'd need to select an additional cell to get it to
trigger).


HTH, and regards,
Zack Barresse

 

[HotRod]

I've tried Target.Cell.Address but that only gives me the cell that just
received focus, I could try to calculate backwards but I'm not sure if I can
account for the user pushing UP, DOWN, RIGHT, LEFT, TAB etc. Any ideas?

 

[zackb]

I know about the Target.Address. That's why I declared the variable ...

Dim rngLast As Range

You need to copy all of the code as it was posted. You will error the first
time you run it because ..

rngLast.Address

will not have been set. It works for me. If you need a workbook example
emailed, give me your email address. It works just fine for me.

Regards,
Zack Barresse

 

[HotRod]

Thanks I actually have your example working. I posted that response before
your post appeared. I still can't believe that I need to use an "On Error"
statement to get through the code, there has to be a better way.

 

[zackb]

If there is a better more efficient way then I do not know about it. I
don't see how you are going to get much simpler than that.

 

[HotRod]

The problem is that I don't like to use "On error Resume next" "On Error
Goto" in the main code since this could be a problem when running my regular
code. I can't believe that MS just didn't add a "got focus" and "lost focus"
procedures. Would have made things a lot simpler.

 

[Tom Ogilvy]

Option Explicit

Dim rngLast As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If not rngLast is nothing then
MsgBox "Current Address: " & Target.Address(0, 0) & vbCrLf & _
"Last Address: " & rngLast.Address(0, 0)
End if
Set rngLast = Target
End Sub