Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ugh! Match/Index problem
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 ------------------------------------------------------------------------ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ugh! Match/Index problem
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 ------------------------------------------------------------------------ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ugh! Match/Index problem
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ugh! Match/Index problem
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ugh! Match/Index problem
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
ugh! Match/Index problem
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index Match Problem | Excel Worksheet Functions | |||
Index Match Problem | Excel Worksheet Functions | |||
INDEX OR Match Problem | Excel Worksheet Functions | |||
Possible index/match problem? | Excel Worksheet Functions | |||
Index Match Problem | Excel Worksheet Functions |