How to convert a Form dropdown selection from index number to actual values? 1

[feipezi]

Hi,

I will have to use Form Control inserts instead of ActiveX Control since the latter will give me problems with the change in definition of monitors. I believe I won't be the first one who has this kind of issues. But as far as features are concerned, ActiveX Cntl is better.

Supposed I have a Combobox of Form Cntl, linked to a range of cells (input range); then specify a cell as Cell Link. But as I pick a value from the dropdown of the Combobox, the value appears in the cell linked will be a ordinal number instead of the value itself. I don't like that. Is there any options that I can use to make it show the value instead of the index? I don't want to write a macro like "IF 1 THEN A, ELSEIF 2 THEN B...".

Thanks in advance.

 

[SkipVought]

HI,

"I don't like that."

Well I don't like it when I break my ankle tripping over a rock!

We must deal with reality...
[tt]
=INDEX(TheListRange,TheLinkCellReference,1)
[/tt]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[feipezi]

Thanks Skip. Good to know using Index() to solve the problems. I used Select-Case like this:

Sub spsource()
Sheet3.Activate
Cells(1, "aa").Select
For Each c In Selection
Select Case c.Value
Case "1"
c.Value = "ARMADA"
Case "2"
c.Value = "LIQUIDHUB"
Case "3"
c.Value = "PROHERANT"
Case "4"
c.Value = "THERACOM"
Case Else
End Select
Next
Sheet2.Activate
End Sub


But Index is better because it will work regardless how long the list will be.

Thanks again.

 

[feipezi]

One more question.

What if the selection on the List switched to Multiple? The index number will be 0. I doubt the Index function will work in that case. What should I do?

 

[SkipVought]

Regarding your code, it is not a satisfactory solution to hard code values in some other list! The control's list source must be used, as it is always possible that the list source could 1) expand, 2) contract, 3) re-order.

Regarding a multi-select: you must loop thru the list to determine which elements are selected. So how do you intend to use these multiple selections?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[feipezi]

I did not hard-code the list. I used another application to create the list for me. Still, it's not as good as Index() since I can say INDEX(A:B,1,2) and no need to worry about how many rows Column A or B has.

I need multiple selection to be used in ADO SQL Where clause like 'select total from datasource where code in ("X","Y","Z")...' I can set up in ("X","Y","Z") but how can I get the 3 values based "0"? Every time as you pick "Multi" as Selection type, the index will be zero, no matter how many entries selected.

Any way to get around?

Thanks.

 

[SkipVought]

Function MultiSelectInList()
    Dim i As Integer, xl As Application
    Set xl = Application
[b]'[highlight #FCE94F]MyList[/highlight] is the named range for the Listbox List Source[/b]
'[b]Your control here >>>>>>>>>>>>>>+[/b]
'[b]                                |[/b]
'[b]                                V[/b]
    With ActiveSheet.Shapes("[b]List Box 2[/b]").OLEFormat.Object
        For i = 1 To [[highlight #FCE94F]MyList[/highlight]].Count
            If .Selected(i) Then
                MultiSelectInList = MultiSelectInList & "'" & xl.Index([[highlight #FCE94F]MyList[/highlight]], i, 1) & "',"
            End If
        Next
        MultiSelectInList = Left(MultiSelectInList, Len(MultiSelectInList) - 1)
    End With
    Set xl = Nothing
End Function

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[feipezi]

Thanks Skip.

It looks like Excel built-in functions, like Index(), Match(0), alone cannot handle the situation.

Btw, is your code going to work on Form Inserts, not ActiveX?

 

[SkipVought]

My code is for Form controls, as you previously stated.

It looks like Excel built-in functions, like Index(), Match(0), alone cannot handle the situation.

You need to know when to use a box wrench, an open end or a crescent.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

I would slightly simplify Skip's code and work only with list:

    With ActiveSheet.ListBoxes("List Box 2")
        For i = 1 To .Count
            If .Selected(i) Then
                MultiSelectInList = MultiSelectInList & "'" & .List(i) & "',"
            End If
        Next
        MultiSelectInList = Left(MultiSelectInList, Len(MultiSelectInList) - 1)
    End With

combo

 

[feipezi]

Skip's code works but Combo's does not: run-time error 438.

Thanks again folks.