Previous Value/contents of cell 1

[PCX]

Is there a function or VBA method to retrieve the previous contents of a changed cell without using the UNDO command?

I guess Excel97 knows what the previous cell contained or it would not have an UNDO command but can I retrieve that value via VBA and without physically UNDOing the cell?

Thanks

 

[SkipVought]

Hi,
This is the MS Office forum as opposed to the VBA forum. But I don't know of any way to get the previous value in a cell, unless you use a VBA approch.

It involves using several Worksheet Events, the ChangeSelection and Change events. You get to these event code by activating the VBE (alt+F11) and double clicking the appropriate Sheet Object in the Project Browser.

In the code window for the sheet ojbect, from the Object dropdown (upper left) select Worksheet. From the Procedure dropdown, select the SelectionChange or Change event code stubs.

In the Worksheet_SelectionChange you can capture a value upon a change in selection -- ie PrevText = Target.Value. If you declare PrevText as Public in the Sheet object, the value can be evaluated by Worksheet_Change to see if PrevValue = Target.Value

Let me know if you want to proceed in this direction. Skip,
metzgsk@voughtaircraft.com

 

[PCX]

Yes that is exactly what I wanted to do in excel.

I have a "Worksheet_SelectionChange" in the worksheet already so adding to this would be the way to go unless there is another way that would be better.

I am new to VB but familiar with excel4 macro language so any help in this area is appreciated.

 

[SkipVought]

Here is some code in Worksheet_SelectionChange and Worksheet_Change to identify the previous value in a cell. With this approch, you can only get 1 previous value. That does not seem as powerful as UNDO.
It requires that

Public Prev()

be declared in a module.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim Cell As Range, i
    i = 0
    For Each Cell In Target
        i = i + 1
        If Prev(i) = Cell.Value Then
            MsgBox "same"
        Else
            MsgBox Prev(i)
        End If
        Prev(i) = Cell.Value
    Next
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Dim Cell As Range, i
    ReDim Prev(1 To Target.Count)
    i = 0
    For Each Cell In Target
        i = i + 1
        Prev(i) = Cell.Value
    Next
End Sub

Hope this give you a basis for proceeding Skip,
metzgsk@voughtaircraft.com

 

[PCX]

Wow, I just got in and tried it and it works just like I needed it to. This is awesome!!!

I'm not sure I understand the logic behind it but it sure does work great.

Thanks again.