[Jeremy Gollehon[_2_]]

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

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

[Jeremy Gollehon[_2_]]

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

[Jeremy Gollehon[_2_]]

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

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

-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

[Jeremy Gollehon[_2_]]

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

[Jeremy Gollehon[_2_]]

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



Dick Kusleika wrote:
Jeremy

Sorry about that, I screwed that up. What I meant was

If IsError(Application.Match(CurrVal, .List,0))

But I think that will cause a problem because CurrValue will always
be a string. So you would need this before

If IsNumeric(CurrVal) Then
CurrVal = CDbl(CurrVal)
End If

If IsError...

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.


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