ugh! Match/Index problem

[Jeremy Gollehon]

I am trying to allow my dropdown to "remember" the value it was on before
..List gets updated and then reposition itself after the update. The code
below works perfectly if .List is made up of Strings. However, it does not
seem to be working when .List is filled with numbers. I need help thinking
my way through this one. I need .List to just be numbers. Any thoughts?
Maybe the problem isn't what I think it is.

Thanks!
-Jeremy

------------------------------------------------------------------------
With UserForm1.Combobox1
ActiveSelection = .Value
.Clear
For i = 12 To IIf(MoCount > 36, 36, MoCount - 1)
.AddItem i
Next i
.ListIndex = IIf(MoCount > 36, 12, 0)
On Error Resume Next
.ListIndex = _
Application.Match(ActiveSelection, Application.Index(.List,0, 1), 0) - 1
On Error GoTo 0
.Enabled = True
End With
------------------------------------------------------------------------

 

[Dick Kusleika]

Jeremy

You should be able to set the Value property of the combobox instead of the
ListIndex. This example works whether the array is strings or numbers:

Private Sub CommandButton1_Click()

Dim CurrVal As Variant

CurrVal = Me.ComboBox1.Value

Me.ComboBox1.Clear

Me.ComboBox1.List = Array("st1", "st2", "st3", "st4")

Me.ComboBox1.Value = CurrVal

End Sub

Private Sub UserForm_Initialize()

Me.ComboBox1.List = Array("st1", "st2", "st3", "st4")

End Sub

 

[Jeremy Gollehon]

Dick,

Thanks for the reply, and it works as promised.
My only problem is that if the combobox list is filled with numbers that
don't include CurrVal then .Value is still set. And I don't want it to be
set if its not an option in .List.

Here's your example set up to mimic my situation:
----------------------------------------------------------------------------
Private Sub CommandButton1_Click()
Dim CurrVal As Variant

With Me.ComboBox1
CurrVal = .Value
.Clear
.List = Array(5, 6, 7, 8)
On Error Resume Next
.ListIndex = _
Application.Match(CurrVal, Application.Index(.List, 0, 1), 0) - 1
'.Value = CurrVal
End With

End Sub

Private Sub UserForm_Initialize()
Me.ComboBox1.List = Array(1, 2, 3, 4)
End Sub
----------------------------------------------------------------------------

The .ListIndex one liner (two with the error code) works great because it
errors and moves on when a match isn't found. It errors no matter what when
working with a .List of numbers though.

The .Value property is set whether its found in .List or not.
Anyway, I can get around this with a loop, but wish it didn't have to come
to that.

Thanks again!
-Jeremy

Jeremy

You should be able to set the Value property of the combobox instead
of the ListIndex. This example works whether the array is strings or
numbers:

Private Sub CommandButton1_Click()

Dim CurrVal As Variant

CurrVal = Me.ComboBox1.Value

Me.ComboBox1.Clear

Me.ComboBox1.List = Array("st1", "st2", "st3", "st4")

Me.ComboBox1.Value = CurrVal

End Sub

Private Sub UserForm_Initialize()

Me.ComboBox1.List = Array("st1", "st2", "st3", "st4")

End Sub

<snip>

 

[Jeremy Gollehon]

Thanks Dick, but I get a type mismatch just trying:

Debug.Print IsError(Application.Match(CurrVal, _
Application.Index(.List, 0,1),0))

-Jeremy

 

[Jeremy Gollehon]

Thanks Dick, but I get a type mismatch just trying:

Debug.Print IsError(Application.Match(CurrVal, _
Application.Index(.List, 0,1),0))

I ended up going with:
------------------------------------------
.ListIndex = 0
For i = 0 To .ListCount - 1
If .List(i) = CurrVal Then
.Value = CurrVal
Exit For
End If
Next i

 

[Jeremy Gollehon]

Or you could just go with what you have. Sometimes the long way is

the best way. Especially when you're trying to figure it out later.

I think agree in this case. Thanks again.

-Jeremy