Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Capturing a change BEFORE leaving a cell

  Asked By: Jaymz    Date: Oct 11    Category: MS Office    Views: 1912
  

This is pretty basic stuff, but it doesn't operate as described.
Hopefully I am using all the correct terminology...

According to the description, the Change event fires the Named_Change
() subroutine when the contents of a cell changes. I use this event
all the time, but not in the following way.
Desired operation:
User enters an abbreviation of a word in a cell (basically the first
letter). When User hits Enter (Excel auto moves down), or arrows out
of the cell, I want to grab that cell's value and put the full word
in. Going from initial letter to full word is a no brainer; I have
that functioning.

However, in the _Change() routine, when I get the ActiveCell.Value
(or Row & Column), I get the the cell just moved *to*, not the one
just changed! Therefore, it appears that the _Change event doesn't
actually occur until the cell *Selection* changes! That's supposed
to be another event. Very frustrating.


I played a bit with a Method called "Previous" but can't understand
the description nor get it to function. KeyUp could be used, but
MSDN, or was it Help, says that KeyUp only works in a Form. I don't
want to use a Form. It is much easier (user friendly) if the user
simply changes things in the cells rather than opening a Form for
each record to be changed.

I'd like to allow the user to simply enter in cells and then auto
correct as they go along. I *could* wait until later and update all
the fields after they all are filled, but it'd be nicer to have the
full word pop into the cell when the user hits Enter.

Any ideas?

Share: 

 

14 Answers Found

 
Answer #1    Answered By: Mansur Bashara     Answered On: Oct 11

I don't know the details of your Named_Change subroutine, but something like the
following may work for you:

Public PrevCell As Range

Private Sub Workbook_Deactivate()
Set PrevCell = Nothing
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)
If Not PrevCell Is Nothing Then
If Len(PrevCell.Value) > 0 Then
Call Named_Change(PrevCell)
End If
End If
Set PrevCell = ActiveCell
End Sub

Sub Named_Change(Rng As Range)
'Expand the text in Rng
Rng.Value = Rng.Value & "X"
End Sub

This method uses a public object variable to keep track of the previous cell.

Copy & paste this into the ThisWorkbook code page of a workbook. Every cell
you change  (after the initial cell) gets an 'X' appended to it as soon as you
move to a different cell, unless you left the cell  empty. You can replace this
dummy Named_Change functionality with the real thing.

By examining the length of the value of PrevCell, you can determine if it is
empty or if it has already been expanded.

 
Answer #2    Answered By: Farah Khan     Answered On: Oct 11

I'll examine this.

FYI:
In my Named_Change() subroutine, if I do:
Debug.Print "Row = "; ActiveCell.Row ; " Col = ";ActiveCell.Row.Column
' OR
Debug.Print "Value = "; ActiveCell.Value

I get the changed _to_ cell, not the one where entered something and
then hit enter.

 
Answer #3    Answered By: Eline Bakker     Answered On: Oct 11

That didn't take long. Realizing I can have a "range" variable,
makes this method easy, though I could have simply saved the row  and
col as well.
Your method is sort of "brute" force. I was hoping I could use a
built-in method to capture the cell  after the value changed, but
before the selection changed. That's what the _Change event  claims
to do...

I did something similar to keep the same cell selected after doing a
sort on a complex data field I saved the column  and contents, then
stuffed a never-to-be-encountered garbage-key text into the cell,
then scanned for that garbage after the sort and replaced the real
contents. Perhaps crude, but it keeps the same cell selected after
the sort. P.S. If the selection was in the sort-key column, I picked
an adjacent column so the garbage wouldn't muck up the sort. It sure
is neat when the stuff  you figure out works.

 
Answer #4    Answered By: Harriet Hughes     Answered On: Oct 11

I decided to give this a whirl and part way through troubleshooting
my disfunctional code and probable mis-use of a range variable, I
remember reading something probably on MSDN about the variable
called "Target" which gets automatically populated in the _Change Sub
parameter list.
This would be from ther DUH! department, then.
The change  event properly captures the changed cell(Range) in the
variable "Target" then the _Change event  fires even if the ActiveCell
(object?) (which you now have access to in the sub) is the one you
moved *to*. Makes some sense as when you are actually *in* the change
sub, you are in the new cell.

 
Answer #5    Answered By: Blandina Garcia     Answered On: Oct 11

The change  handler actually has a Target parameter. There's a nice little
example in the help that sets the text of every changed cell  to blue.

The code from the help:

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Font.ColorIndex = 5
End Sub

Being a perverse type, I decided to lock things up a bit. My change to the
sub

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Font.ColorIndex = 5
Target.Activate
End Sub

It still turns it blue, but immediately reactivates it too.

Adding this to the code

MsgBox Target.Value & " vs " & ActiveCell.Value

throws up a problem, though.

Active cell is empty, cell to the right is also empty. Type A and hit right
arrow. MsgBox gives "A vs". Go back. Type B and hit enter  (assuming you
have enter set not to move the cursor). MsgBox gives "B vs B".

I.e. Target has the new value, not the old value. I don't remember the
original question, so am not sure whether this is a problem or not.

 
Answer #6    Answered By: Addison Campbell     Answered On: Oct 11

That's what I said and it is working now (Auto-correcting an entry
when user  hits enter  or "arrows" out of a cell). Using "Target" did
the trick.

> Private Sub Worksheet_Change(ByVal Target As Range)
> Target.Font.ColorIndex = 5
> Target.Activate
> End Sub
>
> It still turns it blue, but immediately reactivates it too.
>
> Adding this to the code
>
> MsgBox Target.Value & " vs " & ActiveCell.Value
>
> throws up a problem, though.
>
> Active cell  is empty, cell to the right is also empty. Type A and
hit right arrow. MsgBox gives "A vs". Go back. Type B and hit
enter (assuming you have enter set not to move the cursor). MsgBox
gives "B vs B".
>
> I.e. Target has the new value, not the old value. I don't remember
the original question, so am not sure whether this is a problem or
not.

If I read that, it is strange, but my stuff  works ok now.

The original question -- I was trying to use ActiveCell in the _Change
() event  handler sub and I was getting the cell changed TO rather
than the cell changed FROM (Enter changes cell). I ASSUMED that if
the _Change() event fires when a value in a cell changes (not all
that poor an assumption) that the cell just changed would still be
the ActiveCell when you are *IN* the _Change() sub... but
Noooooo. Target refers to the cell changed, but ActiveCell is the
new cell. I guess the cell is changed first *THEN* the value is
changed in the previous cell... Crummy Microsoft.

 
Answer #7    Answered By: Aaleyah Khan     Answered On: Oct 11

>> The change  handler actually has a Target parameter. [...]
>
> That's what I said ...

Well, no, it isn't. :-)

You said "The change event  properly captures the changed cell(Range) in the
variable "Target" then the _Change event fires ..." and this isn't quitre
the same thing.

There is no variable called Target that is used to captuyre this
information. There is a parameter called Target that passes a reference to
the cell  that has been changed.

I updated the information so that the original requester wouldn't go looking
for a global variable called Target.

It is also worth remembering, though, that Target doesn't contain
information captured from the changed cell. It is a reference to the
changed cell itself. This is significant. It means that you can change the
contents of the target cell directly (through the reference) instead of
needing to navigate back to the cell (by asking Target for its location) and
then changing it.

> ... and it is working now (Auto-correcting an entry
> when user  hits enter  or "arrows" out of a cell). Using "Target" did
> the trick.

Good to hear.

> If I read that, it is strange, but my stuff  works ok now.

No, it's probably the way I wrote it. Part of my reply was discussing
whether you should get the "before" contents  or the "after" contents from
the cell. When that becomes a problem is when you want to say "you
shouldn't have changed that and I'm going to change it back". You don't
have access to the original contents of the cell. On a small number of
occasions, the lack of the original value has been a pain for me.

> The original question -- I was trying to use ActiveCell in the _Change
> () event handler sub and I was getting the cell changed TO rather
> than the cell changed FROM (Enter changes cell). I ASSUMED that if
> the _Change() event fires when a value in a cell changes (not all
> that poor an assumption) that the cell just changed would still be
> the ActiveCell when you are *IN* the _Change() sub... but
> Noooooo. Target refers to the cell changed, but ActiveCell is the
> new cell. I guess the cell is changed first *THEN* the value is
> changed in the previous cell... Crummy Microsoft.

There are possibly two separate questions here.

First question is whether the event should fire before the focus changes or
even before the cell changes. In my opinion, yes, as early as possible.
The earlier it's done the more opportunity you have to reject (a) the change
of contents, and (b) the focus change.

(While the actual need for that question was sort-of removed by the Target
parameter, it's still a valid question. Other languages give earlier
"change" events - Excel is a bit lacking in this. Perhaps they can give us
a "BeforeChange" event to go with the other "Before" events.)

Second question is whether you should be given the "before" or the "after"
of the cell. Actually, it would be nice to get both - a copy of the
"before" and a reference to the cell so that the "after" can be adjusted.

Unfortunately, it's easier to simply pass a reference to the changed cell,
so that is what you get. Fortunately, the Target parameter passes the cell
and the cell can tell you almost everything you need to know (except its
"before" contents.

 
Answer #8    Answered By: Marta Kim     Answered On: Oct 11

Target is a range object, and you can use the .Count
property to return the number of cells  in it.

 
Answer #9    Answered By: Shaun Thomas     Answered On: Oct 11

I hope Excel 97 has that. Wonder how I missed that. I tried as
many of the Intellisense suggestions as I thought might be useful and
may have passed that by.

 
Answer #10    Answered By: Akins Massri     Answered On: Oct 11


I'll answer this as I read it, top-to-bottom, so my answers might be a
little disjointed ...

> Please bare with me, I want to understand and am not trying
> to be a wise guy.

Not a problem. I'm glad you didn't think I was just trying to be picky,
too.

> I still have problems telling a parameter from a method as well as
> which methods apply to which objects.

Firstly, we have functions and subroutines (subs) in VBA. Under normal
circumstances, they should be called by these terms, although it's common to
use either term "function" or "subroutine" in a generic term when
perhaps the other one is meant.

Particularly, when code is separated out into subroutines or functions, the
generic term "subroutine" is often used for both. (In some other languages,
there are no subroutines, just functions, and that term will be used
throughout.

To be precise on this, however, a function returns a value and can be used
inside an expression. A subroutine  doesn't return a value and is a
statement in itself, possibly with a "call" in front of it.

Then we have parameters. These are what is inside the parentheses of a
function or subroutine declaration and are supplied in the call to the
function or subroutine. The term "formal parameter" is applied to the one
that is in the definition of the subroutine or function (and is used inside
that routine); the term "actual parameter" is applied to the one that is in
the call to the subroutine or function.

The term "method" is an object-oriented term and signifies the method with
which you communicate with the object. It applies equally to methods
defined with function and with sub in VBA. So a method is a public function
or sub defined in an object/class.

Private functions or subs defined in a class module should not be called
methods (although it is hard to get out of the habit of calling them
"private methods") and should really be called "private functions" (or
subs).

Private variables declared in the main part of a class should be called
attributes, although other names are used as well, including "private
variables". Public variables declared in the main part of a class are
usually called by some sort of rude name (they are not "nice") but can be
called "public attributes", "public variables" or even "method variables".
Easiest way to get around what to call them is not to have them.

Re which methods apply to which objects. There is nothing to assist you
here (except for the excellent help you get from excel  when you type the
dot). The methods that an object has depends entirely on the original
programmer, although there are some conventions that are reasonably well
adhered to in the Excel objects.

> Intellisense shows parameters and methods which don't necessarily apply to
> a given object, so this
> is a pitfall.

There's no shortcut to this. You've just got to learn them or use help a
lot.

> I try things and the solutions I arrive at usually
> make sense when I get to the proper form/syntax (and things
> subsequently make sense reading MSDN, when MSDN was confusing in the
> beginning).

As you write more and more VBA code, some of the constructs will stick in
your mind, and for others, you'll be able to remember what you did that in
last time, and go there and grab  it out. I have written more lines of VBA
than is probably healthy, and I still very frequently go back and grab code
out of previous programs. Basically I never throw anything away.

> I'm used to assembler and old Basic. OO has new and
> strange concepts and terminology.

Unfortunately, the object implementation in VBA is "so near but so far" from
more traditional OO that it is sometimes more confusing than a "proper" OO
language is.

When I first started writing user  classes in VBA, I had a fair bit of
trouble, despite many years of using OO languages.

> So, yes I used the wrong term, but "Target", indeed provides some
> kind of a link to the changed cell  and I get it now - a reference to
> it. I was able to print simply "Target" and see the contents  of the
> cell and print Target.Address and see the reference in RC format. So
> that's cool.

Actually, their choice of "Target" as the parameter name doesn't really
help the situation. What is the "target" of an event  that fires after a
cell has been changed and after the focus has moved? Even a simple
parameter name such as "Cell" would have been more intuitively
understandable for this event. So feel a bit of annoyance at the programmer
that dreamt up that formal parameter name.

> However, here's a confusing point and this is part of the misleading
> part. Doing:
> Debug.Print Target
> results in the "contents" of the cell (If it has any) showing in the
> immediate window.

Yes, this is the default for a cell (the .Value method call) when a simple
value is needed. I have found similar problems in code written by
professional programmers. They omit a method call and then wonder why they
aren't actually getting what they wanted.

The following code

Dim A as Range
Dim B as String
Set A = Cells(1, 1)
B = Cells(1, 1)

is quite legal (unless I stuffed it up), yet the thing being assigned is
very different in the two circumstances. At least, the presence here of a
Set gives you an indication. But, if the cell is being passed as an actual
parameter of a subroutine call, there isn't even a Set.

> From this it is easy to infer that target "represents" the *content*
> of the cell. So it looks like either a variable -- Ok it's a
> reference with a default definition of the value (IF there is
> something there). So I would call this a mixed metaphor..whatever,
> I have it working...

Your point is a good one. If I had been designing the CBA Excel interface,
I would have not provided a default method call. It is no effort to put
.Value on the cell, and sometimes makes all the difference for clarity.

> Except that my above observed behavior obscures this.

Yes. That's why I raised it. It's one thing that the VB environment
doesn't really help you with, and you need to keep constantly in mind.

> I'm not debating,

(No, I'm not taking it that way)

> I'm just pointing out the source of confusion.

And you are right. And it's not just a trap for the amateur.

> This is part of the VBA-assumes-what-you-want-and-gives-it-to-you paradigm
> which makes it easy to use at other times (like polymorphism), or
> gets you in trouble when that wasn't what you wanted.

....

> New_Variable = Target [[ results in VALUE being transferred ]]

Provided New_Variable is a "value" variable, rather than an object variable.

> I think I got your previous, but writing these explanation isn't
> easy. However, what you describe isn't my problem. I don't need the
> BEFORE-CHANGE value.

Yes, I think that's where it went wrong. When I was writing the original, I
had the "before" in mind as a requirement too. When I re-read the question,
I was clear that I was mistaken.

> This is where I was going wrong. _Change() fires (as I wanted to
> understand it) in response to a change  in value, therefore I think it
> should fire the routine  after the change in value, but before the
> focus change to a new cell -- seems logical to me, perhaps.
> Therefore any reference to ActiveCell should be BEFORE the Focus
> change as well. There is a _ChangeSelection() event which (in my
> opinion) should fire after both the value/content change and the
> focus change. I'll drop this and move on to my next related question.

Your reasoning is quite sound. Just to make things worse ... inside a form,
the change event fires for a text box every time  the box is changed in any
way (i.e. for each character typed into it). This is almost unusable and
leaves you longing for something more like the cell change event. :-(

> Re-cap.
> Enter "c" in a cell. _Change() fires and all is well in Mudville.
> Target allows me to do the magic to change this to "CW" (I take care
> of upper/lower case too).
> HOWEVER, if I CLEAR a GROUP of cells  (select more than one cell then
> Alt-E, A,C), when the _Change() event handler fires, I get an ERROR
> of "Type mismatch". for this:
>
> Debug.Print " Target = "; Target

I think this is just telling you that a multi-cell range doesn't have any
sort of default that Debug.Print can use. I think you should be able to do
something like

Dim Cell as Range
For Each Cell in Target
Debug.Print " Target (part of) =", Cell
Next Cell

or simply

Debug.Print " Target (1st cell) =", Target.Range("A1")

However, if the area you cleared was not a single block of cells, then I
don't think the .Range("A1") method of grabbing the top left of a range
would work. You'd probably need to look at the Areas that Target covers.

In a practical sense, you'll need to look at Target to see if it is giving
you a single cell's change or something more sinister. For instance, one of
my spreadsheet templates has code in it to cater for a person who tends to
type in lower case a lot and to abbreviate other things that are not
supposed to be abbreviated in these sheets. So, in ThisWorkbook, I have

Private Sub Workbook_SheetChange(ByVal Sheet As Object, ByVal Cell As Range)
Call FixAbbreviations(Cell)
End Sub

And then

Public Sub FixAbbreviations(ByVal Cell As Range)
If VarType(Cell) = vbString Then
Select Case Cell.Value
Case "tbd", _
"tba", _
"kp", _
"wp", _
"ds", _
"n/r", _
"n/a": Cell.Value = UCase(Cell.Value)
Case "nil", _
"NIL": Cell.Value = "Nil"
Case "NR", _
"nr": Cell.Value = "N/R"
Case "NA", _
"na": Cell.Value = "N/A"
End Select
End If
End Sub

The important statement here is

If VarType(Cell) = vbString Then

It does two things for me. Firstly it stops me looking for strings in
non-string cells. But more importantly, it ensures that I'm only checking a
single cell.- not an area. There are other ways of checking for this, of
course - this is just the one I chose at the time as being simple and
actually doing two checks in one.

> This is baffling.
> I did the Debug.Print because I got the same error where I was using
> Target to get to the value (which is now null due to clearing).
> (again, changing the code to use Target as a reference may solve
> this)
>
> I tried to do various things to detect if "Target" represents >1 cell
> & the ActiveCell is null, but couldn't find any Intellisense choice
> which told me this. "Target.Address" gives me the range (i.e
> $C$1:$D$3) of the cleared Range.
> What I wound up doing was to check if Target represented more than
> one cell by using the Properties (I think) of RowHeight and
> ColumnWidth then simply not execute the offending code.

Possibly the easiest attribute to check is Count. It will tell you how many
cells are selected.

 
Answer #11    Answered By: Sally Pierce     Answered On: Oct 11

Thanks. I'll print & read at leasure. I see you're explaining things
I alread am familiar with but you don't know my knowledge base, so
that's ok except you waste your bandwidth.

P.S. To confuse things further... I see that the automatically
gnereated SUB for the _Change event  makes Target By Value not By Ref.

 
Answer #12    Answered By: Erma Henry     Answered On: Oct 11

I'm tryin' to keep it short...

Holy criminy! (and thanks to Hutch for beating you to the punch)
... Your last comment is what would have answered my original Q.. (:-)


> > I still have problems telling a parameter from a method as well
as which methods apply to which objects.

Unfortunately, not knowing my knowledge, you spent most of your time
on things I know. Sub vs. function and passing parameters to/from
subs.

I was referring to VBA objects and the "after-the-dot" thingys. As I
think you said, these are not identified - as a method or parameter -
in the Intellisense "pop-up-after-the-dot-menu", and more
importantly, whether they actually are supported by *that* specific
object. What was initially misleading was the fact the the objects
the programmer forms *are* in that pop-up list, implying (or rather I
was inferring) it includes *supported* methods/parameters.
In my mind a parameter is some "characteristic" of the object and a
method is some operation  that the object supports/performs (code it
has and runs)
Some are obvious, like the color of something probably would be
considered by all as a parameter. This is all foggy and open to
intrepretation, of course.


> The term "method" is an object-oriented term and signifies the
method with which you communicate with the object.

communicate makes some sense, though I think of it (from some very
minimal coaching on OO concepts) as some operation that the object
has to / can perform rather than you having to perform it or asking
about some characteristic.



> Re which methods apply to which objects. There is nothing to
assist you here (except for the excellent help you get from Excel
when you type the dot).

There is the "Help" help, which ain't so bad some of the time,
but it uses uSoft terminology and if you don't know all the
terminology, Help it "aint". The concept usually is within
understanding except the terminology interferes with the message.




> As you write more and more VBA code, some of the constructs will
stick in your mind, ...

Naturally.



>... I had a fair bit of trouble, despite many years of using OO
languages.

My trouble was not 'hampered' by that type of unfortunate
experience. (:-)



[... snippitty...snippitty...snip...]

> Possibly the easiest attribute to check is Count. It will tell you
how many cells  are selected.

How I missed that as a possible solution when I carefully (yea,
right) tried all the "after-the-dot-thingys" that appeared and looked
like possibilities, but there it was, last night (in 97) when I
looked ... and the code is already changed... I just "knew" there
was a built-in function(sic) for that; it seemed like a natural.

The By Val / By Ref explanations given for VBA work for me, but I
rarely use that aspect. Weird or not I have 99% Public variables.
That way I never have to remember what I called something, and I
think my application (not to mention brain) fits that paradigm well
(don't need a lot of multi purpose subs). Whatever "floats your
boat", eh? You software geeks think differently and that's OK.

 
Answer #13    Answered By: Fabiola Ferrrari     Answered On: Oct 11

Yes, you are correct  on ByVal.

It's an interesting conundrum, that one. The reference (i.e. the pointer in
C++ terms) can be passed by value and changes made via it will still change
what is pointed to. ByRef really tells the compiler that you can change  the
pointer itself.

To retain my own sanity (if it's not already too late), I always use ByRef
on these pointers as a reminder to myself that the object pointed to can be
changed. It's an arbitrary convention and certainly not required by the
language.

Re bandwidth ... don't worry about bandwidth, I type quickly. And I was not
sure what you knew, as your question made me feel you might be confused
between the term "parameter" and a subroutine. So I stepped back a bit to
make sure we were talking approximately the same language.

 
Answer #14    Answered By: Edith Mcdonald     Answered On: Oct 11


I'm sure that 97 has that.............

 
Didn't find what you were looking for? Find more on Capturing a change BEFORE leaving a cell Or get search suggestion and latest updates.




Tagged: