CODEWITHSUNDEEP
excelvbacombobox
darkZone
darkZone

Reputation: 713

VBA - Get OLD Value of Combobox

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

Upvotes: 5

Views: 8770

Answers (3)

zin
zin

Reputation: 21

I use excel combo boxes alot, and have developed a number of useful features like: * save & load combo box data from the registry or a hidden "APP_DATA" worksheet * add a new permanent combo box item by entering a new value and pressing * allowing editing of combo history by double-clicking on the box * clearing all combo history by erasing any currently showing item and pressing

These are just some ideas to get you going, the code is fairly simple, I just wrote some simple subs for: * load a combo box from a history string * dedup a delimited string * event to trap a for "new item" or "erase items" function * event to trap a for "edit items" function

When a new item is added, I just append it to the history string, and dedup it just in case. History strings are saved or loaded from registry on initialize and terminate, or as they are changed, and initialize also populates the combos. I always assumed there would be an easy way to do this, since I see so many combo boxes maintaining history (I limit to the latest 24 items), but I never found any code, so I just made my own. In some apps, even double-clicking a worksheet cell value can populate or CSV-append to a combo box, or a command button can prompt for and load a series of cells into a CSV list into combo, very useful.

Upvotes: 2

lfrandom
lfrandom

Reputation: 1023

This is a bit more work, but what I've done in the past is create a private variable which contains the value.

Basically on the change event, you do what you need to do with the old value, then you update the old value variable to the new value.

Private oldValueComboBox as String

Private Sub ComboBox1_Change()
    ' Do whatever you need to do with the old value, in this case msgbox
    msgbox oldValueComboBox

    ' Set the old value variable to the new value
    oldValueComboBox = ComboBox1
End Sub

You can also use a static variable as another post mentions. If you use a static variable, it is only usable within the scope of the combobox change, if you use a private variable it is visible to the entire form.

Upvotes: 2

Doug Glancy
Doug Glancy

Reputation: 27478

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.

Upvotes: 6

Related Questions