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
Dick Kusleika wrote:
Jeremy
How about
If Not IsError(Application.Match(CurrVal, Application.Index(.List, 0,
1), 0)) Then
.Value = CurrVal
End If
It will set the value if it's in the list, but won't do anything if
it's not.
--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.
"Jeremy Gollehon" wrote in message
...
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
Dick Kusleika wrote:
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