Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

How can I find the previous value of a cell after it has been chang

  Asked By: Karina    Date: Dec 22    Category: MS Office    Views: 821
  

I have a macro on the change event of the worksheet. When say a value
of a cell is changed by the user from 'apples' to 'oranges', in my
code I would like to be able to find out the previous value.

For example the change event triggers and activecell.value
returns 'oranges', is there any method such as
activecell.previousvalue that would return 'apples'.

I have looked through all the properties of the cell object but can't
find a value that would return this. It would be similar to
the .oldvalue that performs this task in MS Access VBA.

If there is no answer but someone has a clever work around please let
me know!

Share: 

 

3 Answers Found

 
Answer #1    Answered By: Bonifaco Garcia     Answered On: Dec 22

not an expert but my suggestion would be to use the before change  event to
save the old value... or maybe it's the on enter event.... not exactly
sure.. again i'm not an expert ...

 
Answer #2    Answered By: Estella Mitchell     Answered On: Dec 22

Yes, there is a way. Use the worksheet  SelectionChange Event. Within the
VBE go to the worksheet in question, for example  Sheet1. In the drop
down box above the VBE environment, where it normally says "General",
select "Worksheet". In the drop down box to the right select
"SelectionChange".

Or just copy and paste this into the Sheet Module.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

The "Target" value grabs anything the user  clicks on, within a cell  that
is. So now something like this...

Dim sHold as Variant
sHold = Target.Value

...will grab what's in the cell before you change  it.

 
Answer #3    Answered By: Felicia Hill     Answered On: Dec 22

Just for reference, if anyone else is interested - declare a module
level variable in the General declarations section to pick up the
value.

You can then access  this value in the change  event to see what the
old and new values are.

One last point to consider, you will get an error if you have this
code and the user  selects more than one cell  so a good idea to trap
for target.cells.count > 1.

 




Tagged: