Original Cell Value (Excel) 1

[SonicBoomBand]

Is there a before worksheet event method to capture an original cell value before the user changes it?

I would appreciate any help.

 

[Nikita6003]

Hiya,

have a look at useful Chip Pearson's site on Worksheet events:

http://www.cpearson.com/excel/events.htm

Cheers
Nikki
Look, mommy, I'm flying!

 

[Nikita6003]

of course that *should* read: "Chip Pearson's useful site"

D'oh

(Not that Chip isn't useful himself)


Cheers
Nikki
Look, mommy, I'm flying!

 

[SonicBoomBand]

Chip Pearson's Site unfortunately, didn't contain what I was after.

Thanks Nikki for pointing me to it. I think that may be useful in the future.

 

[Nikita6003]

Andrew,

you can use the Worksheet_SelectionChange event to pick up the old value. Store it somewhere on a hidden sheet & use it when needed

HTH


Cheers
Nikki
Look, mommy, I'm flying!

 

[SonicBoomBand]

Nikki

I've had a look at this and can't seem to find the code to pick up the old value. 'Target.Value' returns my new figure.

These worksheets_change/SelectionChange are only triggered once the new figure is in.

Do you by any chance have any code that will return me the old value?

Andrew

Andrew Chamberlain
National Grid Transco

 

[Loomah]

Hi Andrew
Have a look at this thread and see if you can adapt it - thread68-848477

This gives a history of selected cells' addresses. all you should need to do is substitute the address property for value. If you only need to check on certain cells of a range of cells then have a look at the intersect method.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[SonicBoomBand]

Loomah

Unfortunately, the solution in the thread provided cannot be altered for my scenario, as far as I can tell.

Still got the problem that target.value gives the updated value and not the previous.

A suggestion I have heard is to trap the mouse click and run a routine from there. I've had a look at this and will need some help. Does anybody have any ideas on this?

Andrew



Andrew Chamberlain
National Grid Transco

 

[Loomah]

Andrew
I'm not sure why you're have a problem with this!
The code in that thread uses the basic principles that Nikki has already suggested.

What should happen is that if you uses the selecton change event you will get the value of the cell before you have time to change it.

As an example add some values to the range A1 : C10 of a worksheet and add the following code to the worksheet module. Select different cells to see the value of what you select. Then try changing the values.

Unfortunately I have to go now and I'm not sure if I'll be back today

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:C10")) Is Nothing Then
    MsgBox "CHANGE" & vbCrLf & Target.Address & vbCrLf & "Value = " & Target.Value
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Store this info on another sheet?
If Not Intersect(Target, Range("A1:C10")) Is Nothing Then
    MsgBox "SELECTION CHANGE" & vbCrLf & Target.Address & vbCrLf & "Value = " & Target.Value
End If
End Sub

Good Luck
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[SonicBoomBand]

Ahhhh Solution.

Please ignore my last message, I was just being stupid. It's Monday morning.

Code is as follows:-

------------------------------------------------------------
dim t as string
----------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
t = Target
End Sub
----------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
msgbox "Value has changed from " & t & " to " & target
End Sub
------------------------------------------------------------

Thanks to both Nikki and Loomah for pointing me in the right direction.

Andrew

Andrew Chamberlain
National Grid Transco

 

[SonicBoomBand]

Loomah

I started my last message before I read yours. Thanks for the code and your help.

Andrew

Andrew Chamberlain
National Grid Transco