Combo Box - Get previous and currently selected values 1

[philipose]

Hi,
I had a question on how to retrieve the currently selected value from the drop down list of the Activex Combo box and existed value of an Activex Combo Box. In the events, Click and Change the values of

cboName1.Value
cboName1.List(cboName1.ListIndex)

appear to be the same. Does anyone have an idea on this matter? I am using Excel 2000.

 

[SkipVought]

Hi,

cboName1.Value IS the current value.

What's the question? If you want the PEVIOUS value, you would have had to stored it somewhere.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[philipose]

Hi Skip,
Thanks for the reply. I want the current value and the previous value from the Activex Combo Box.

As you suggested one way is to store it into a temporary variable (on the AfterUpdate or LostFocus event, I do not know which might be the best. Please do send suggestions if any)

Another way(I do not know if such an event exists in VBA) is if there is an event like a BeforeUpdate where the cboName1.Value is still the previous value and
cboName1.List(cboName1.ListIndex) is the newly selected value.

Thanks for any suggestion in advance

 

[fumei]

Here is some simple code to do what you want at run-time. However, if what you want to is to really store the value, that is another question. This works dynamically, while the form is active. The current and previous values are displayed as labels. You of course could do wahtever else you want with the values.

Option Explicit
Dim sWas As String, sIs As String

Private Sub ComboBox1_Change()
sWas = sIs
sIs = ComboBox1.Value
Label1.Caption = "Current combobox value is: " & sIs
Label2.Caption = "Current combobox value is: " & sWas
End Sub


Private Sub UserForm_Initialize()
ComboBox1.AddItem "one"
ComboBox1.AddItem "two"
ComboBox1.AddItem "three"
ComboBox1.AddItem "four"
ComboBox1.AddItem "five"
ComboBox1.AddItem "six"
ComboBox1.ListIndex = 0
      sIs = ComboBox1.Value

' initalize with same values, as no choice
' or change, has been made
   Label1.Caption = "Current combobox value is: " & sIs
   Label2.Caption = "Last combobox value was: " & sIs
End Sub

Gerry

 

[philipose]

yo fumei,
That was mindblowing. great one

 

[fumei]

Glad I could help.

Gerry