0

I have data validation as list for some cells (possible values are "Enrolled", "Waitlisted", "Cancelled"). I need to execute some code if the value of these cells changes, only if the new value is not the same as the existing one. Question is, how can I get Excel to compare the previous value of the cell with the current one.

I tried this solution (How do I get the old value of a changed cell in Excel VBA?) but it didn't work. What am I missing? Here is some sample code. Currently, it changes the cell colors even if I enter the same value.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim previous_value As String

previous_value = oval

Select Case Target.Value
    Case Is = "enrolled"
        If previous_value = Target.Value Then
        MsgBox "you entered the same value"
        Application.EnableEvents = False
        Application.Undo
        Application.EnableEvents = True

        Else 
        Target.Interior.Color = vbBlue

        End If
    Case Is = "waitlisted"
' (....etc.)
End Select


End Sub

Public Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim oval As String

If Selection.Cells.Count = 1 Then
    oval = Target.Value
End If
End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Alex Sirbu
  • 37
  • 1
  • 6
  • I checked the other answer you listed compared to yours. You need to have your variables outside of the macros but within the sheet so they can be stored for comparison. – pgSystemTester Apr 12 '19 at 05:32

2 Answers2

1

If you use something like this below code, you can save the most recent clicked instance in a named range and then check it against whatever the user entered. Obviously, this goes in the respective sheet code.

Private anOldValue As Variant

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count = 1 Then

    If Target.Value = anOldValue Then

        MsgBox "Same value!"
    End If
End If

End Sub



Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Cells.Count = 1 Then

    anOldValue = Target.Value

End If

End Sub
pgSystemTester
  • 8,979
  • 2
  • 23
  • 49
  • Hi @PGCodeRider! I can't seem to be able to adapt your solution for what I'm trying to achieve. 1) I am trying to prevent the user from selecting by mistake the same value from the drop-down 2) but if they select one of the two other possible values, some other code needs to run. This solution only accomplishes item 1. It didn't work when I tried to use it within each case of the Select case statements. Nor I can use Else If statements (If target.value = "waitlisted etc.), because I don't know which 2 out of 3 possible values I need to check for. I don't know if that made sense. – Alex Sirbu Apr 12 '19 at 05:56
1

Here is the final code. Thanks @PGCodeRider for the help!

Private anOldValue As Variant

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count = 1 Then
    Select Case Target.Value
        Case Is = "enrolled"
            If Target.Value = anOldValue Then
               MsgBox "Student already enrolled!"
            Else 'code that needs to happen when "enrolled" is selected
               Target.Interior.ColorIndex = 10
            End If
        Case Is = "waitlisted"
            If Target.Value = anOldValue Then
                MsgBox "Student already waitlisted!"
            Else  'code that needs to happen when "waitlisted" is selected
               Target.Interior.ColorIndex = 20
            End If

        Case Is = "cancelled"
            If Target.Value = anOldValue Then
                MsgBox "Student already cancelled!"
            Else   'code that needs to happen when "cancelled" is selected
                Target.Interior.ColorIndex = 30

            End If
    End Select
End If

End Sub



Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Cells.Count = 1 Then

    anOldValue = Target.Value

End If

End Sub
Alex Sirbu
  • 37
  • 1
  • 6