Find Previous Value in combobox

[skipjakk]

Is there an easy way to determine the previous value in a combobox? I need to determine the difference between the previous and current value, is there a way to do this easily?

 

[xlbo]

You would need to store all selections that the combobox has made somehere on a sheet and then test against that cell...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[ettienne]

By previous value do you mean the value preceding the current selected value, or the previously selected value?
You can store the previously selected value in a variable and then calculate the difference. To get the preceding value look at the .ListIndex property of the combo box.

 

[skipjakk]

Previous selected value. For example, if "April" was selected in the combobox previously (or listbox,etc), and someone selected "September", I would want to be able to calc the difference between the two....

 

[xlbo]

Are there any conditions for WHEN the last select should take place? It sounds like you are trying to do a calc between 2 distinct choices in a combobox - what if someone accidently selects a month and then selects the correct one?

I would use the combo_change event to store each selection in a specified cell - all you then need to do is check back against the value of that cell?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[fumei]

Interesting how you Excel types always assume it is Excel.

Two separate references to "cell".

It very well may be for Excel, but the OP has NOT mentioned anything (at all) about the application this is in. Nor has it been mentioned whether the control is on a Sheet (if Excel), or in a document (if Word). For all we know, this combobox could be on a userform.....in Word.

BTW: I am not being critical, I am trying to be somewhat amusing.

skipjakk, Geoff (xlbo) is correct though. The only way would be to take the current selection and put its value into a variable. His question is also significant.

"Are there any conditions for WHEN the last select should take place? "

The when is crucial.

If the current value is put into a variable on _Change, then..that variable will always be the current selection. So, do you want to test whenever there IS a change? In which case, yes use _Change, but test at the very start, as in:

Sub ComboBox1_Change()
If [i]variable[/i] <> 0 Then
   '  .... your calculation/comparing code
   '  [b]then[/b] set the variable for the new value
Else
   [i]variable[/i] = ComboBox1.ListIndex
End If

This is assuming you are using the numeric value of ListIndex, rather that the text values of each item ("April", "September").

In other words, if the variable is NOT 0, then it has been _Changed before, and the test is to be done.

If it is 0 (the Else), then just set the variable value.

The next _Change, the variable will not be 0, and so it will be tested.

Note that as you are going to be using multiple executions of _Change, the variable should be Public in the module, not Dim'd in the Sub.

Or....if this is Word (...just ribbing you Geoff), you could store the pervious selected item as a document Variable, rather than a Public variable in the module.

faq219-2884

Gerry
My paintings and sculpture

 

[xlbo]

np Gerry - you are of course correct - I just automatically assume Excel ;-)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question