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
------------------------------------------------------------------------

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

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Jeremy Gollehon" wrote in message
...
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,

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