welshraz
New Member
- Joined
- Apr 29, 2016
- Messages
- 39
- Office Version
- 365
- Platform
- Windows
Afternoon,
I have a column (A) with eight different text drop down options. I need a code that will allow me to track each time the drop down is changed to a different option. I want each change to be tracked in a different cell so that I can see how each line has progressed - e.g. from Received to Query to Invalid etc.
I have the following code which tracks any change in any cell on the row, but I want it to only track the changes from column A (starting at A3):
Essentially, I want every change made in column A tracked in column AW, AX, AY and so on. There will be no more than 10 changes to an individual cell in column A.
Thanks.
I have a column (A) with eight different text drop down options. I need a code that will allow me to track each time the drop down is changed to a different option. I want each change to be tracked in a different cell so that I can see how each line has progressed - e.g. from Received to Query to Invalid etc.
I have the following code which tracks any change in any cell on the row, but I want it to only track the changes from column A (starting at A3):
VBA Code:
Option Explicit
Private selRng As Range
Private selPrevValue As Variant
Private Sub Worksheet_Activate()
If Target.Cells.Count = 1 Then
Set selRng = Selection
selPrevValue = selRng.Value2
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim selCurValue As Variant
On Error GoTo ErrLoc 'added to ensure events are always turned back on
If Target.Cells.Count = 1 Then
Application.EnableEvents = False
If selRng Is Nothing Then
Set selRng = Target
selCurValue = selRng.Value2
Application.Undo
selPrevValue = selRng.Value2
Target.Value = selCurValue
If selPrevValue <> selCurValue Then
Storeprevvalue selRng, selPrevValue
End If
Else
If selPrevValue <> Target.Value Then
Storeprevvalue selRng, selPrevValue
End If
selPrevValue = Target.Value2
Set selRng = Target
End If
End If
ErrLoc:
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count = 1 Then
Set selRng = Selection
selPrevValue = selRng.Value2
End If
End Sub
Sub Storeprevvalue(rng As Range, prevValue As Variant)
If Not IsEmpty(prevValue) Then
Me.Cells(rng.Row, Me.Columns.Count).End(xlToLeft).Offset(, 1).Value = prevValue
End If
End Sub
Essentially, I want every change made in column A tracked in column AW, AX, AY and so on. There will be no more than 10 changes to an individual cell in column A.
Thanks.
Last edited by a moderator: