Oops, I meant to remove that line. The problem is with the "OldValue"
variable.
BTW, OldValue is declared as a string; OldRange is declared as a
Range. I don't think I'm using OldRange properly, but that's another
issue.
Paul Schreiner wrote:
> I think I've found the source of your problem.
>
> it has to do with the line:
> Set OldRange = Target
>
> in this case, once you've issued this command,
> OldRange will "automatically" update to whatever the
> value of "Target" is.
> the command doesn't HAVE to be reissued.
> You have essentially "ASSIGNED" to variable OldRange
> to be whatever Target happens to be.
>
> If you're wanting OldRange to retain it's value
> unless or until you change it, get rid of the word "set"
> (notice that:
> OldValue = Target.Cells.Value
> works fine without the "set")
>
> does that help?
>
> Paul
>
>
>
>
> ________________________________
> From: Doug <dsrmccl...@gmail.com>
> To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> Sent: Wednesday, September 9, 2009 11:55:15 AM
> Subject: $$Excel-Macros$$ Capture value of new Target
>
>
> I'm trying to troubleshoot this bit of sample code so that I can apply
> the solution to my larger project.
>
> On a worksheet I have a WorksheetChange event and a Selection_Change
> event. What I am trying to do is capture the value of the new target
> cell after the Selection_Change event.
>
> This code works fine:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> If Not Intersect(Target, Range("a1:z1")) Is Nothing Then
> ActiveCell.Select
> End If
>
> End Sub
>
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>
> On Error Resume Next
>
> If Intersect(Target, Range("a1:z1")) Is Nothing Then
> Exit Sub
> Else: OldValue = Target.Cells.Value
> Set OldRange = Target
> End If
>
> End Sub
>
>
> However, if I change "ActiveCell.Select" to "ActiveCell.Offset
> (0,1).Select" to skip over a cell, it doesn't work.
>
> But here's the thing that REALLY puzzles me:
> If I step through the code in the debugging window, it seems to
> work fine- that is, the cell skips to the proper cell and the OldValue
> is set to the value of that destination cell (the new Target).
> But, then, if I type "Print OldValue" in the Immediate pane, it
> returns the value in the cell immediately to the right of the initial
> cell, and if I call the variable elsewhere it has that same incorrect
> value!
>
> Can someone please enlighten me? Where is this code short circuiting?
>
> Thanks as always,
> Doug
--~--~---------~--~----~------------~-------~--~----~
----------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~----------~----~----~----~------~----~------~--~---
