Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Address of Cell that lost focus?
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Address of Cell that lost focus?
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" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Address of Cell that lost focus?
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Address of Cell that lost focus?
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" wrote in message ... 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Address of Cell that lost focus?
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Address of Cell that lost focus?
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. -- Regards, Zack Barresse, aka firefytr "HotRod" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Address of Cell that lost focus?
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Address of Cell that lost focus?
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 -- Regards, Tom Ogilvy "HotRod" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lost Focus apply to cell | Excel Programming | |||
Validate Data in VBA "How to get lost focus cell address" | Excel Programming | |||
Lost Focus - Applying to cell | Excel Programming | |||
Textbox focus lost | Excel Programming | |||
Cell Lost Focus | Excel Programming |