VBA - Get OLD Value of Combobox

Question

I would like to get the old value of a ComboBox when the combobox value will change.

I have tried something like:

Private Sub ComboBox1_Change()
        Application.EnableEvents = False
        newVal = ComboBox1.Value
        Application.Undo
        oldVal = ComboBox1.Valu
End Sub

or

Private Sub ComboBox1_Change()
        Application.EnableEvents = False
        newVal = ComboBox1.Value
        ComboBox1.Undo
        oldVal = ComboBox1.Valu
End Sub

but it seems not to work...

Thanks

Answer 1

You could use a Static variable that holds its value between calls to the ComboBox1_Change event:

Private Sub ComboBox1_Change()
Static OldValue As String

With Me.ComboBox1
    Debug.Print "Value: "; .Value; " Old Value: "; OldValue
    OldValue = .Value
End With
End Sub

If you need to access OldValue outside of the Change event, use a module-level variable, as described by @Ifrandom.