Tracking a cell

[calecote]

I want to be able to measure the progression of a cell that is in a dropdown in Excel. Once the cell "closes" it is important but it is more important to see what the cell was before it was coded as closed. Is there a way to see what the last item in that cell was? maybe put it in another cell?

 

[Jerry Sullivan]

Hi calecote,

That can probably be done with VBA. Can you provide some clarification?

1. There are several types of dropdowns in Excel. Are you referring to a Data Validation drop down, an ActiveX combobox, or a Form control?

2. What do you mean by the cell "closes"? I'm guessing you mean after the user selects an item, the dropbox list disappears and the selected value is shown in the cell. Is that right?

3. Do you just want to store the previous value, or by progression, do you need a longer history? If the latter, how many values max?

 

[calecote]

Hi Jerry,

It is a Data Validation drop down list.

I mean the term close in a business setting. When I am working on a list and I put the cell in "closed" because I do not need to work on it anymore I want to be able to measure what the cell was before it was closed. A progression or simply the last one will work. I just want to be able to measure what the cell was coded as from the list before it was coded as closed.

 

[Jerry Sullivan]

This seems to work. It assumes you have a your DV drop down in A1. Each time the value in A1 changes the previous value will be placed in B2.

Place this into the Sheet Code Module for that Worksheet.

Public sPREVIOUS As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    sPREVIOUS = Target.Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    
    If Target.Value <> sPREVIOUS Then
        Application.EnableEvents = False
        Range("B1").Value = sPREVIOUS
        sPREVIOUS = Target.Value
        Application.EnableEvents = True
    End If
End Sub